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.

sql-server

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

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

What is new features in Javascript ES2020 ECMAScript 2020

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Random Password Generator Online

HTML Color Picker online | HEX Color Picker | RGB Color Picker

What is SQL dba Server?

Computer developers and administrators are able to manipulate the data within the database with the use of SQL statements. These statements enable the programmer to perform certain tasks, such as updating data, retrieving data and filtering...

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

How to become a SQL DBA: Database Administrator

Database Administration consists of everything essential for managing a database. This makes it accessible as per your need. The DBA is the person who maintains, backs up and ensures the data generation and consumption. You can access them by...

What is SQL Server DATEDIFF() method?

In this article, we will be focusing on SQL Server DATEDIFF() function in detail.