Update query using Subquery in PLSql Server

Question

I compared table1 data table2 data, and filtered data which was not existed in table1. I want to make both tables rows count equal. For that, I need to set is_active = 0 in table2, where the data was not existed in table1.

Example::

UPDATE table2 
SET table2.is_active = 0 
WHERE (SELECT table2.user_name 
       FROM table2 
       WHERE table2.is_Active = 1 
         AND table2.user_name NOT IN (SELECT table1.user_name 
                                      FROM table1 
                                      WHERE table1.is_Active = 1));

==table1==      ==table2==
'A', 'B', 'C'   'A', 'B', 'C'
'A', 'E', 'C'   'M', 'N', 'O'
'A', 'E', 'D'   'A', 'E', 'D'
                'A', 'E', 'D'

In above example, table 1 and table2 contains similar data except 'M', 'N', 'O', I want set this row into is_active = 0 state.

score 0 · Answer 1 · answered Jun 29 '22 at 18:43

To me, update with not exists seems to be one option.

Sample data:

SQL> select * from table1;

COL1  COL2  COL3   IS_ACTIVE
----- ----- ----- ----------
a     b     c              1
a     e     c              1
a     e     d              1

SQL> select * from table2;

COL1  COL2  COL3   IS_ACTIVE
----- ----- ----- ----------
a     b     c              1
a     e     c              1
a     e     d              1
m     n     o              1   --> IS_ACTIVE should be set to 0

Update:

SQL> update table2 b set
  2    b.is_active = 0
  3    where not exists (select null from table1 a
  4                      where a.col1 = b.col1
  5                        and a.col2 = b.col2
  6                        and a.col3 = b.col3
  7                     );

1 row updated.

Result:

SQL> select * from table2;

COL1  COL2  COL3   IS_ACTIVE
----- ----- ----- ----------
a     b     c              1
a     e     c              1
a     e     d              1
m     n     o              0   --> IS_ACTIVE is set to 0

SQL>

Update query using Subquery in PLSql Server

1 Answers1