CODEWITHSUNDEEP
sqloracle-database
neewbee
neewbee

Reputation: 13

SQL Update query to update one column from 2 tables

I have tables table1 and table 2 Here is the select statement.

SELECT table1_column1, table1_column2, table1_column3, table2_column1,
        table2_column2, table2_column3
FROM table1
   , table2
WHERE table1_column1 = table2_column1
  AND table1_column2 = 'A'
  AND table1_column3 <> table2_column3

I get results where table1_column3 and table2_column3 have different values. which is correct.

I want to update and replace all of table2_column3 values with table1_column3 my update sql statement gives me ORA-00936: missing expression error.

Upvotes: 1

Views: 83

Answers (1)

user5683823
user5683823

Reputation:

Whenever you must update (or insert into) a table based on values from another table, consider using the merge statement instead of update/insert:

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

In your case, you seem to be trying to do this:

merge into table2 t2
  using table1 t1
    on (t1.column1 = t2.column1 and t1.column2 = 'A')
when matched then update set column3 = t1.column3
  where column3 != t1.column3
;

If instead you want to do this with an update statement, this may help:

Update with joins

Upvotes: 2

Related Questions