| UPDATE with a join. [message #10416] |
Wed, 21 January 2004 10:45  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
I try to run this batch, but I receive an error from the Oracle server (Oracle 8i):
UPDATE tableA
SET a.column_2 = b.column_2
FROM tableA a, tableB b
WHERE a.column_1 = b.column_1;
I try to update/merge the column_2 of the tableA based upon the value of the column_2 from the tableB.
What is wrong with this simple UPDATE statment?
Thank you very much in advance!
Regards,
Patrick Tahiri.
|
|
|
|
| Re: UPDATE with a join. [message #10419 is a reply to message #10416] |
Wed, 21 January 2004 11:01  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
UPDATE tableA
SET column_2 = (select b.column_2
FROM tableB b
WHERE b.column_1 = a.column_1);
If there is not a matching row in b, the column in a will be set to NULL. If you want to preserve values on failed lookups in b, then add to the outer statement:
where exists (select null
from tableB b
where b.column_1 = a.column_1) |
|
|
|
|
|
| Re: UPDATE with a join. [message #10425 is a reply to message #10416] |
Wed, 21 January 2004 14:06 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
If appropriate keys exist between the two tables, you could also try:
UPDATE ( SELECT a.column_2 AS oldvalue
, b.column_2 AS newvalue
FROM tableA a, tableB b
WHERE a.column_1 = b.column_1 )
SET oldvalue = newvalue; |
|
|
|
|
|
| Re: UPDATE with a join. [message #10467 is a reply to message #10419] |
Sat, 24 January 2004 09:52 |
Sudhakar
Messages: 34
Registered: May 2002
|
Member |
|
|
If you want to preserve values on failed lookups in b,try the following
UPDATE tableA a
SET column_2 = NVL((select b.column_2 FROM tableB b WHERE b.column_1 = a.column_1),column_2);
regards,
Sudhakar
|
|
|
|
| Re: UPDATE with a join. [message #10485 is a reply to message #10467] |
Mon, 26 January 2004 07:59 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
It depends on the ratio of successful vs. failed lookups. If there are many failed lookups, you generally would want to avoid the overhead of the update on tableA (even though it is not changing the value, there is rollback, redo, etc.). If just a few rows might not have a match, this is a viable option.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
