Update query in oracle sql

Question

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

MT0 · Accepted Answer · 2022-09-07 10:34:10Z

1

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

answered Sep 7, 2022 at 10:34

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update query in oracle sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related