CODEWITHSUNDEEP
sql-servert-sql
Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Update statement updates all records in the table and not the ones related to the condition

I have this following table called STG_Params :

Schema Table           DataBase Flag_Full
dbo    Users           LocalDB  1
dbo    bankInformation LocalDB  0
dbo    Language        LocalDB  0
dbo    Employee        LocalDB  1
dbo    WorkPermit      LocalDB  1
dbo    Roles           LocalDB  1
dbo    School          LocalDB  1
dbo    Nationality     LocalDB  1
dbo    Degree          LocalDB  1

I want to update records having TableName like below and set their Flag_Full to 0 : 

UPDATE STG_Param SET Flag_Full = 0 
WHERE EXISTS (SELECT m.* from STG_Param m where m.[Table] in ('Users','Employee','Roles',WorkPermit')

When I execute the query it updates all the records with Flag_Full=1 including the table names not mentioned in the where condition.

Upvotes: 0

Views: 44

Answers (2)

Thom A
Thom A

Reputation: 95554

You have 2 queries here that make up your full statement. The first is UPDATE STG_Param SET Flag_Full = 0, and this will update a row provided that the following sub-query (which is not correlated) returns at least one row: SELECT m.* from STG_Param m where m.[Table] in ('Users','Employee','Roles',WorkPermit').

This means that if at least one row in the table STG_Param has the value 'Users','Employee','Roles' or 'WorkPermit' for the column Table, then all rows in the table STG_Param will be updated, as the EXISTS will return TRUE.

What you need here is just a simple WHERE:

UPDATE STG_Param 
SET Flag_Full = 0 
WHERE [Table] IN ('Users','Employee','Roles',WorkPermit');

Upvotes: 3

ankyskywalker
ankyskywalker

Reputation: 1462

You can give an alias to the table you are updating and use the where condition directly. You can also use joins before the WHERE clause if you need to get the list of tables based on a condition in another table.

UPDATE STG 
SET STG.Flag_Full = 0 
FROM STG_Param STG
WHERE STG.[Table] IN ('Users','Employee','Roles','WorkPermit')

Upvotes: 3

Related Questions