Update query in oracle sql-CodePudding

create table table1(accountno number);
insert into table1  values (1);
insert into table1 values (2);
insert into table1 values (3);
insert into table1 values (4);
insert into table1 values (5);
insert into table1 values (6);

create table table2(accountno number,check_y_n varchar2(20));

insert into table2 (accountno)  values (4);
insert into table2 (accountno) values (5);
insert into table2 (accountno) values (6);
insert into table2 (accountno) values (7);
insert into table2 (accountno) values (8);
insert into table2 (accountno) values (9);

I need below two update query in single query using joins. Can anyone help me on this

UPDATE TABLE2 SET check_y_n ='YES' WHERE accountno IN (SELECT accountno FROM TABLE1);

UPDATE TABLE2 SET check_y_n ='NO' WHERE accountno NOT IN (SELECT accountno FROM TABLE1);

CodePudding user response：

Use a CASE expression:

UPDATE TABLE2
SET check_y_n = CASE
                WHEN accountno IN (SELECT accountno FROM TABLE1)
                THEN 'YES'
                ELSE 'NO'
                END;

db<>fiddle here