Sql update a column with join

Question

there are two tables named TEST1 and TEST2 each has two columns, ID and NAME as below.

TEST1
ID  NAME
1   BOB
2   MIKE
3   TOM
4   TAMA

TEST2
    ID  NAME
    1   RAMIN
    2   RAHIM
    3   RONA
    4   ZAK
    6   ZENO
    7   YOURA
    8   SONE

i want to update table TEST1 (NAME) Column with TEST2 (NAME) Column values if ID in table TEST1 matched with ID in table TEST2.

running below query will result this error msg "ORA-00933: SQL command not properly ended"

UPDATE tbl_test1 
SET    tbl_test1.NAME = tbl_test2.NAME 
FROM   tbl_test1 
   INNER JOIN tbl_test2 
           ON tbl_test1.id = tbl_test2.id

hi Mureinik i searched stackoverflow but can not find it, can you plz send me the link where does it is answered, coz i checked found a bit different that could not solve my problem — Mellad Qarizada, Jan 29 '17 at 08:20
This brief article in the documentation shows the proper syntax for updating through a join in Oracle, and the most common error you may run into (when you use the correct syntax), the reason for it, and how to fix it: http://stackoverflow.com/documentation/oracle/8061/update-with-joins#t=201701291448187158868 — , Jan 29 '17 at 14:48

Gurwinder Singh · Answer 1 · 2017-01-29T11:28:24.240

0

Best to use MERGE here:

merge into tbl_test1 t
using tbl_test2 t2 on (t.id = t2.id)
when matched then
  update set t.name = t2.name;

edited Jan 29 '17 at 11:28

answered Jan 29 '17 at 08:18

Gurwinder Singh

38,557
6
51
76

now it is throwing ORA-01779: cannot modify a column which maps to a non key-preserved table – Mellad Qarizada Jan 29 '17 at 10:50
UPDATE ( SELECT t1.name t1name, t2.name t2name FROM TBL_TEST1 t1 INNER JOIN TBL_TEST2 t2 ON t1.ID= t2.ID ) set t1name = t2name – Mellad Qarizada Jan 29 '17 at 10:50
@Mellad I've updated the answer. Please try now – Gurwinder Singh Jan 29 '17 at 11:28

Sql update a column with join

1 Answers1