Home » SQL & PL/SQL » SQL & PL/SQL » A tricky case in EXISTS
A tricky case in EXISTS [message #417703] Tue, 11 August 2009 04:00 Go to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Say I have three tables A,B & C

I need to update table A for the records that exists in any of the B or C tables.

update A set A.col1 =1 where
( EXISTS (
SELECT 1
FROM A)
OR EXISTS (
SELECT 1
FROM B)
)

is this logic right?
is there any alternative for this?
pls help

regards,
Nataraj
Re: A tricky case in EXISTS [message #417706 is a reply to message #417703] Tue, 11 August 2009 04:04 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you need to correlate the sub-queries to the update statement or you'll update every row in A.
Re: A tricky case in EXISTS [message #417715 is a reply to message #417703] Tue, 11 August 2009 04:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rtnataraj wrote on Tue, 11 August 2009 11:00
Say I have three tables A,B & C

I need to update table A for the records that exists in any of the B or C tables.
update A set A.col1 =1 where 
(   EXISTS (
              SELECT 1
                FROM A)
 OR EXISTS (
              SELECT 1
                FROM B)
       )

is this logic right?


The first part (exists select 1 from A) will always be true, if there are rows in A, so what you basically are saying is
"Update all rows in A, if there are rows in A"
The OR-condition has no use, since if "select 1 from A" does not return any rows, there are no rows to be updated.
You might as well remove the whole where-clause.
Re: A tricky case in EXISTS [message #417718 is a reply to message #417715] Tue, 11 August 2009 04:31 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

this is the query that I need to optimise.

UPDATE a a
   SET a.col1 = 1
 WHERE (EXISTS (SELECT 1
                  FROM b
                 WHERE b.col2 = a.col2) or EXISTS (SELECT 1
                                                     FROM c
                                                    WHERE c.col2 = a.col2));


regards,
Nataraj

Re: A tricky case in EXISTS [message #417724 is a reply to message #417718] Tue, 11 August 2009 04:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is something completely different.
This one does seem to make sense; do what I did with your previous query: try to translate it in <use your native language here> bit by bit and see if it holds.
Re: A tricky case in EXISTS [message #417738 is a reply to message #417718] Tue, 11 August 2009 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd be tempted to rewrite it as an Update only Merge:
MERGE INTO a
USING (SELECT col_2 FROM b
       UNION
       SELECT col_2 FROM c) src
ON (a.col_2 = src.col_2)
WHEN MATCHED THEN UPDATE SET a.col_1 = 1
Re: A tricky case in EXISTS [message #417892 is a reply to message #417718] Wed, 12 August 2009 00:56 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I think of this
UPDATE a a
   SET a.col1 = 1
 WHERE a.col2 IN (SELECT col2
                    FROM b
                  UNION ALL
                  SELECT col2
                    FROM c);


See the difference
SQL> insert into a 
  2  select rownum,rownum+3
  3   from dual connect by level <100000;

99999 rows created.

SQL> commit;

Commit complete.

SQL> insert into b
  2   select rownum,rownum+7
  3   from dual connect by level <100000;

99999 rows created.

SQL> insert into c
  2   select rownum,rownum+9
  3   from dual connect by level <100000;

99999 rows created.

SQL> commit;

Commit complete.

SQL> savepoint s1;

Savepoint created.
SQL> set timing on
SQL> UPDATE a a
  2     SET a.col1 = 1
  3   WHERE (EXISTS (SELECT 1
  4                    FROM b
  5                   WHERE b.col2 = a.col2) or EXISTS (SELECT 1
  6                                                       FROM c
  7                                                      WHERE c.col2 = a.col2));

99995 rows updated.

Elapsed: 00:12:13.37
SQL> rollback to s1;

Rollback complete.

Elapsed: 00:00:01.20
SQL> UPDATE a a
  2     SET a.col1 = 1
  3   WHERE a.col2 IN (SELECT col2
  4                      FROM b
  5                    UNION ALL
  6                    SELECT col2
  7                      FROM c);

99995 rows updated.

Elapsed: 00:00:01.39

[Updated on: Wed, 12 August 2009 01:18]

Report message to a moderator

Previous Topic: sql query
Next Topic: passing arrays
Goto Forum:
  


Current Time: Mon Dec 05 07:08:37 CST 2016

Total time taken to generate the page: 0.10016 seconds