CODEWITHSUNDEEP
mysqloracle-database
Martin Spa
Martin Spa

Reputation: 1534

Updating a table in Oracle with two tables in where clause

I have the following query, which works in MySQL, and I need help&hints to rewrite it for Oracle 11g. (I'm new to PL/SQL..)

update table1 n, table2 t
set n.col1 = t.col1, n.col2 = t.col2
where t.col3 = n.col3;

Upvotes: 0

Views: 4241

Answers (2)

xdazz
xdazz

Reputation: 160923

Try something like this.

UPDATE 
  (
      SELECT n.col1 AS n_col1, n.col2 AS n_col2, t.col1 AS t_col1, t.col2 AS t_col2 
      FROM table1 n 
      JOIN tabble2 t ON n.col3=t.col3
  ) a 
SET a.n_col1 = a.t_col1, 
    a.n_col2 = a.t_col2

Upvotes: 2

Ollie
Ollie

Reputation: 17568

UPDATE table1 n
   SET (n.col1, n.col2) = (SELECT t.col1, t.col2
                             FROM table2 t
                            WHERE t.col3 = n.col3)
 WHERE EXISTS (SELECT 'x'
                 FROM table2 t
                WHERE t.col3 = n.col3);

Upvotes: 1

Related Questions