CODEWITHSUNDEEP
sqlsql-server
Vikas J
Vikas J

Reputation: 887

SQL Update single column depending on conditions of multiple joins

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:

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: enter image description here

Upvotes: 0

Views: 144

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5141

You can use below simple queries,

  1. 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);

  2. 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);

  3. 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;

Upvotes: 1

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

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

Upvotes: 0

Related Questions