What is the difference between this two SQL?

Group,

I would like to update table test from test1. But I do not understand why we need to use where exists subquery

SQL> select * from test;

        ID COL1
---------- ----------

         1 a1
         2 b1
         3 c1
         4 d1

SQL> select * from test2;

        ID COL1
---------- ----------

         1 a2
         2 b2
         3 c2
         4

Both two update statement seems to be working; SQL> update test
  2 set col1 = (select col1 from test2
  3 where test.id=test2.id);

4 rows updated.

SQL> select * from test;

        ID COL1
---------- ----------

         1 a2
         2 b2
         3 c2
         4

SQL> rollback;

Rollback complete.

SQL> select * from test;

        ID COL1
---------- ----------

         1 a1
         2 b1
         3 c1
         4 d1

SQL> update test
  2 set col1 = (select col1 from test2
  3 where test.id=test2.id)
  4 where exists
  5 (select 1 from test2 where test.id=test2.id);

4 rows updated.

SQL> select * from test;

        ID COL1
---------- ----------

         1 a2
         2 b2
         3 c2
         4

Is it necessary to use the exists where clause?

Thanks for your help. Received on Wed Dec 05 2007 - 09:33:18 CST