Hi,
I have two tables. Table A and Table B. I'm trying to perform an update on 1 column from table A and 2 columns from table B. I can of course do this with two separate update statements. But I would like to know whether it's possible to perform this with a single update statement. Please find below my two individual update statements.
Let's say Table A's column that I'm trying to update as Column 1 and Table B's columns as Column 2 and Column 3.
Update 1:
update table A set table A.column 1=<value> where table A.column X=<some value> and table A.column Y in (select distinct table B.column Y from table B where table B.column A=<value> and table B.column B=<value> and table B.column C=<value> and table B.time_stamp between '<>' and '<>' and table B.column D in (some values));
Update 2:
update table B set table B.Column 2=<value>, table B.Column 3=sysdate where table B.column A=<value> and table B.column B=<value> and table B.column C=<value> and table B.time_stamp between '<>' and '<>' and table B.column D in (some values));
Thank you!
Sure you can!
Either you can have a separate subquery for each column in the set clause. Or, better, you can set many columns in one go by placing them in parentheses:
create table t1 (
x int, y int
);
create table t2 (
x int, y int
);
insert into t1 values (1, 1);
insert into t2 values (2, 2);
commit;
select * from t1;
X Y
1 1
update t1
set (x, y) = (
select x, y from t2
);
select * from t1;
X Y
2 2
Or you could go with an update only merge:
rollback;
select * from t1;
X Y
1 1
merge into t1
using t2
on (1=1) -- replace with your join clause
when matched then
update set t1.x = t2.x, t1.y = t2.y;
select * from t1;
X Y
2 2