Re: What is the difference between this two SQL?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 5 Dec 2007 08:07:19 -0800 (PST)
Message-ID: <1c506c7b-7572-4456-8e39-5e87a661381f@w56g2000hsf.googlegroups.com>

On Dec 5, 10:33 am, emdproduct..._at_hotmail.com wrote:
> 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.

You will see a difference if ID 4 does not exist in table TEST2. For example:
CREATE TABLE T1 (
  C1 NUMBER(8),
  C2 NUMBER(8)); CREATE TABLE T2 (
  C1 NUMBER(8),
  C2 NUMBER(8));

INSERT INTO T1 VALUES (1,1);
INSERT INTO T1 VALUES (2,2);
INSERT INTO T1 VALUES (3,3);
INSERT INTO T1 VALUES (4,4);
INSERT INTO T1 VALUES (5,5);

INSERT INTO T2 VALUES (1,0);
INSERT INTO T2 VALUES (2,0);

INSERT INTO T2 VALUES (3,0);
INSERT INTO T2 VALUES (4,0); Note that there will be no C1 value of 5 in table T2.

COMMIT; Now, an update without limiting the rows that will be affected: UPDATE
  T1
SET
  C2=(
    SELECT
      C2
    FROM
      T2
    WHERE
      T1.C1=T2.C1);

5 rows updated.

SELECT
  *
FROM
  T1;

        C1 C2
---------- ----------

         1          0
         2          0
         3          0
         4          0
         5

Note the null value that is now present in T1.C2 where C1 is 5. Since there was no matching row in table T2, the value was set to NULL.

ROLLBACK; Now again, this time constraining the rows to be updated: UPDATE
  T1
SET
  C2=(
    SELECT
      C2
    FROM
      T2
    WHERE
      T1.C1=T2.C1)
WHERE
  T1.C1 IN (
    SELECT
      C1
    FROM
      T2);

4 rows updated.

SELECT
  *
FROM
  T1;

        C1 C2
---------- ----------

         1          0
         2          0
         3          0
         4          0
         5          5

Note that the last row, where C1=5 does not have C2 set to NULL.

ROLLBACK; The same query using the EXISTS clause:
UPDATE
  T1
SET
  C2=(
    SELECT
      C2
    FROM
      T2
    WHERE
      T1.C1=T2.C1)
WHERE
  EXISTS (
    SELECT
      1
    FROM
      T2
    WHERE
      T1.C1=T2.C1);

4 rows updated.

SELECT
  *
FROM
  T1;

        C1 C2
---------- ----------

         1          0
         2          0
         3          0
         4          0
         5          5

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Dec 05 2007 - 10:07:19 CST