I know there is no Update-Join statement in PL/SQL. I used to write T-SQL.
I have two tables.
Alex_Table1
---------------------------
PK VAL
---------------------------
1 Value1
2 Value2
3 Value3
Alex_Table2
---------------------------
PK VAL
---------------------------
1 Value1_2
3 Value3_2
I want to update VAL from Alex_Table2 to Alex_Table1 joining by PK column.
In T-SQL, that is simple
update t1 set t1.VAL = t2.VAL
from
Alex_Table1 t1
inner join Alex_Table2 t2 on t1.PK = t2.PK;
And the result is what I expected
Alex_Table1
---------------------------
PK VAL
---------------------------
1 Value1_2
2 Value2
3 Value3_2
My question is how to do in PL/SQL? Thanks!
Update
Thanks Cybernate and ypercube gave me two useful solutions. They both work. However, I want to let you know the performance between these two statements.
My actual table contains 80,000 records and only 3,000 needs update.
MERGE
statement takes 0.533 seconds.
UPDATE EXISTS
statement takes over 3 minutes ( I did not measure the actual time. I terminated my process after 3 minutes.)
The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement. Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.
Oracle Update Statement with Join The answer is pretty straightforward: in Oracle this syntax of UPDATE statement with a JOIN is not supported.
To UPDATE a table by joining multiple tables in SQL, let's create the two tables 'order' and 'order_detail. ' We can update the data of a table using conditions of other joined tables. It is possible to join two or more tables in an UPDATE query.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
Use a MERGE statement:
MERGE INTO Alex_Table1 t1
USING Alex_Table2 t2
ON (t1.PK = t2.PK)
WHEN MATCHED THEN
UPDATE SET t1.VAL = t2.VAL
UPDATE Alex_Table1 t1
SET t1.VAL =
( SELECT t2.VAL
FROM Alex_Table2 t2
WHERE t2.PK = t1.PK
)
WHERE EXISTS
( SELECT *
FROM Alex_Table2 t2
WHERE t2.PK = t1.PK
)
This also works (as long as (PK)
is the primary key of Alex_Table2
):
UPDATE
( SELECT t1.VAL A, t2.VAL B
FROM Alex_Table1 t1
JOIN Alex_Table2 t2
ON t2.PK = t1.PK
)
SET A = B ;
Tested at dbfiddle.uk.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With