Update row value only when all other rows meet the condition

Update row value only when all other rows meet the condition

I'm having some issues with this seemingly simple table. I'm trying to change the value of the NEW_LVL column rows to either 1 (if all the TRAINING_LVL rows have the value 1) or 0 (if one of the TRAINING_LVL rows has the value 2), in this case for NEW_NR value 10.

I'm having some issues with this seemingly simple table. I'm trying to change the value of the NEW_LVL column rows to either 1 (if all the TRAINING_LVL rows have the value 1) or 0 (if one of the TRAINING_LVL rows has the value 2), in this case for NEW_NR value 10.

USER_NR TRAINING_NR TRAINING_LVL NEW_NR NEW_LVL
49867   8400              2        10      0
49867   8490              1        10      1
49867   8480              1        10      1

So far I've been trying to use the CASE method to change it but I can't seem to be able to change it so that it only modifies the NEW_LVL value when all of the rows meet the condition. Instead, it is changing the value for each individual row, if they meet the condition.

UPDATE dbo.MTD
    SET NEW_LVL =
    CASE
        WHEN NEW_NR = 10 AND TRAINING_LVL = 1 THEN 1
        ELSE 0
    END

What would be a better way to do this? I have tried using the HAVING clause, rather unsuccessfully.

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners, Microsoft SQL Server Tutorial, SQL Server Training. Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples. What is DBMS? What is SQL? What is SQL Server? SQL Server Installation

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

Build a CRUD App with ASP.NET Core 2.2 and SQL Server

​ I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created *JudgeMyTaste*, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly. ​ The combination of ASP.NET and SQL Server is probably the most common pairing in the enterprises that use ASP.NET. With ASP.NET Core and SQL Server both being cross-platform, you don’t *have* to run this combination on Windows anymore! I’ll show you how to create a basic CRUD application using ASP.NET Core 2.2 and SQL Server 2017. I’ll be running on Linux, but with the free tools used here, it won’t matter what operating system you’re using! ​ The tools I’ll be using that are available for all platforms are: * SQL Server 2017 (I’ll be running on Ubuntu 18.04) * Visual Studio Code * Azure Data Studio * ASP.NET Core 2.2 ​ Once you’ve got all the tools installed for your platform, let’s rock and roll! ​ ​ ​ ## Scaffold Your ASP.NET Core 2.2 Application ​ No matter the platform you’re on, the ```dotnet``` CLI is available. The commands used here should be the same for everyone. To scaffold the ASP.NET Core 2.2 MVC application, create a new folder for it: I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created JudgeMyTaste, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly.