| A tricky case in EXISTS [message #417703] |
Tue, 11 August 2009 04:00  |
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 #417715 is a reply to message #417703] |
Tue, 11 August 2009 04:21  |
Frank
Messages: 7901
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 #417724 is a reply to message #417718] |
Tue, 11 August 2009 04:53 |
Frank
Messages: 7901
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 |
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 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
