SQL Update single column depending on conditions of multiple joins

Question

In SQL Server 2016, I have 2 tables (Del_Test_Main and Del_Test_Stg) Later will always get the latest list of Active Id's based on which I want to update the main table.

But there are multiple scenarios:

If the main table has an Id that does not exist in the Stg table, then Update IsDelete column of the main table to 1 and update Rootpid only for those rows.
If the main table has an Id that also exists in Stg table, but IsDelete column value is 1 in the Main table then Update IsDelete column to 0 and update Rootpid only for those rows.

Input Query:

Create table Del_Test_Main ( uniqueId bigint identity not null, Id int, Name varchar(50), IsDelete tinyint default 0, RootPid varchar(50) ) insert into Del_Test_Main (Id,Name,IsDelete,RootPid) values (1,'Vj',0,'20190101') , (2,'john',0,'20190101'), (3,'lance',1,'20190101' ), (4,'kate',1,'20190101' ) Create table Del_Test_Stg ( Id int ) insert into Del_Test_Stg (Id) values (1) ,(3) select * from Del_Test_Main select Id as ActiveIds from Del_Test_Stg

Using below update queries I can achieve expected results. But how can I get it done in a single update query for both Scenarios? Also, the count of Id's will be in thousands and millions so also need to consider about the query from a performance perspective.

-- S1 SELECT main.Id AS "List_Of_Ids_to_Update_in_main_AS_1" --UPDATE main --SET IsDelete = 1, RootPid = '20190202' FROM ..Del_Test_Main main LEFT JOIN .. Del_Test_Stg del ON main.Id=del.Id WHERE del.Id IS NULL AND main.IsDelete = 0 -- S2 SELECT main.Id AS "List_Of_Ids_to_Update_in_main_AS_0" --UPDATE main --SET IsDelete = 0, RootPid = '20190202' FROM ..Del_Test_Main main INNER JOIN ..Del_Test_Stg del ON main.Id=del.Id WHERE main.IsDelete = 1

UPDATE:

halfer · Accepted Answer · 2020-05-24 21:53:04Z

You can use below simple queries,

Updates IsDelete to 0 in Del_Test_Main when there is no id in Del_Test_Stg table

update Del_Test_Main set IsDelete = 0 where id not in (select id from Del_Test_Stg);

Updates IsDelete to 1 when there is id in Del_Test_Stg table and IsDelete is 0 in Del_Test_Main table
```
update Del_Test_Main set IsDelete = 1 where IsDelete = 0 and id in (select id from Del_Test_Stg); 
```

Both the operations in one query,

update Del_Test_Main set IsDelete = case when id not in (select id from Del_Test_Stg) then 0 when id in (select id from Del_Test_Stg) and IsDelete = 0 then 1 end;

As I have mentioned in my post, I don't want 2 update queries, I want it to be done using 1 update query for both scenarios to match expected output
Yes, But that's still not giving expected output. It updating all rows. What I want is in single query it should update only expected rows.

Srinika Pinnaduwage · Accepted Answer · 2020-05-24 14:44:20Z

You can use Case Statement

SELECT main.*, del.*, Case when del.Id IS NULL then 'List_Of_Ids_to_Update_in_main_AS_0' when main.IsDelete = 1 and del.Id IS NOT NULL then 'List_Of_Ids_to_Update_in_main_AS_1' Else 'Both Exists and main.IsDelete = 0'End AS mainId --UPDATE main --SET IsDelete = Case when main.IsDelete = 1 and del.Id IS NULL then 0 -- when main.IsDelete = 0 and del.Id IS NOT NULL then 1 End, -- Else IsDelete End, -- RootPid = '20190202' FROM ..Del_Test_Main main LEFT JOIN ..Del_Test_Stg del ON main.Id=del.Id WHERE main.IsDelete = 1 or main.IsDelete = 0

With your output edit, I changed my query

SELECT main.*, del.*, Case when del.Id IS NULL And main.IsDelete = 0 then 'Change IsDelete to 1' when main.IsDelete = 1 and del.Id IS NOT NULL then 'Change IsDelete to 0' Else 'Keep IsDelete as it is ' End AS mainId, Case when del.Id IS NULL And main.IsDelete = 0 then 'Change RootPid to 20190202' When main.IsDelete = 1 and del.Id IS NOT NULL then 'Change RootPid to 20190202' Else 'Keep RootPid as it is ' End AS RootPid --UPDATE main --SET IsDelete = Case when del.Id IS NULL And main.IsDelete = 0 then 1 -- when main.IsDelete = 1 and del.Id IS NOT NULL then 0 -- Else IsDelete End, -- RootPid = Case when del.Id IS NULL And main.IsDelete = 0 then '20190202' -- when main.IsDelete = 1 and del.Id IS NOT NULL then '20190202' -- Else RootPid End FROM ..Del_Test_Main main LEFT JOIN ..Del_Test_Stg del ON main.Id=del.Id

Here is the fiddle

thanks, but your query is not giving expected output. Please Check my post I have added the expected result.

Gordon Linoff · Accepted Answer · 2020-05-25 00:53:37Z

If I understand correctly, you can use a left join and a case expression:

update m set isdelete = (case when s.id is null then 1 when s.id is not null and m.isDelete = 1 then 0 else isdelete end) from del_test_main m left join del_test_stg s on m.id = s.id where s.id is null or s.id is not null and m.isDelete = 1;

Here is a db<>fiddle.

thanks, but your query is not giving expected output. Please Check my post I have added the expected result.
@VikasJ . . . Your sample data explains better what you want to do. I modified the answer.

Collectives™ on Stack Overflow

SQL Update single column depending on conditions of multiple joins

3 Answers 3

3 Comments

3 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

2 Comments

Related