Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PL/SQL Update Join?

Tags:

join

plsql

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.)

like image 310
Alex Yeung Avatar asked Jul 26 '11 00:07

Alex Yeung


People also ask

Can you use join in update SQL?

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.

Can we use joins in update query in Oracle?

Oracle Update Statement with Join The answer is pretty straightforward: in Oracle this syntax of UPDATE statement with a JOIN is not supported.

Can you update a joined table?

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.

What does (+) mean in Oracle join?

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.


2 Answers

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
like image 75
Chandu Avatar answered Nov 16 '22 03:11

Chandu


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.

like image 21
ypercubeᵀᴹ Avatar answered Nov 16 '22 03:11

ypercubeᵀᴹ