I have an update query like following:
update table TABLE1 set COL1 = 'X' where COL2 = 'Y' ---1
Support the values 'X' and 'Y' are fetched from database now TABLE2. E.g.
select COL1, COL2 from TABLE2. ----2
I want to update table TABLE1 with values from TABLE2.
Just to make it more clear, assume that TABLE2 has following values:
Can you please help me in doing this in a single query!
I am using Oracle 11g.
For Oracle, this is the most basic way to do it:
update TABLE1
set COL1 = (select TABLE2.COL1 from TABLE2 where TABLE2.COL2 = TABLE1.COL2)
where COL2 IN (select TABLE2.COL2 from TABLE2);
This can be inefficient in some cases since it could execute a subquery for every row in TABLE1.
Depending on the declaration of primary key or unique constraints on both tables, you may be able to use the updateable inline-view method, which is probably more efficient:
update
(select TABLE1.COL1 as T1C1, TABLE1.COL2 as T1C2, TABLE2.COL1 as T2C1
from TABLE1 join TABLE2 on TABLE2.COL2 = TABLE1.COL2
)
set T1C1 = T2C1;
@Dave Costa's answer is correct, if you limit yourself to update statements. However, I've found that using a merge statement in these situations allows me to do this in a more straightforward manner:
merge into TABLE1
using TABLE2
on (TABLE2.COL2 = TABLE1.COL2)
when matched then
update set TABLE1.COL1 = TABLE2.COL1;
update TABLE1
set TABLE1.COL1 = TABLE2.COL1
from TABLE1
join TABLE2 on TABLE1.COL2 = TABLE2.COL2
(this would work on Sql Server)
table keyword is redundant here update table TABLE1 ?for oracle:
UPDATE Table1 t1
SET (X,Y) = (SELECT X,Y from Table2 WHERE ...YourConditions...)
WHERE ... Another Conditions ...
for mysql, sql-server
UPDATE t1
SET t1.X = t2, t2.Y = t2.Y
FROM Table1 t1, Table2 t2
WHERE t1.Something = t2.Something
UPDATE statement doesn't have a FROM clause.