Prev: Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE
Next: SQLPlus Hangs
From: emdproduction on 5 Dec 2007 10:33
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.

From: Charles Hooper on 5 Dec 2007 11:07
On Dec 5, 10:33 am, emdproduct...(a)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.
 | 
Pages: 1
Prev: Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE
Next: SQLPlus Hangs