How to Update on table with another table

Question

I have two tables table_A and Table_B

Table_A

ID    Col1    Col2   Col3
123    2      A      JIM
456    2      A      JACK

Table_B

ID    Col1    Col2   Col3
123    1       B      KIM
456    1       B      LEE

Query Using:

Update Table_A a, Table_B b
Set  a.Col1 = b.Col1,
     a.Col2 = b.Col2,
     a.Col3 = b.Col3
where a.ID = b.ID

Current Results: Getting error

Required Results:

I want to update value of Table 1 with value of Table 2.

You've tagged this for Oracle and MySQL. Which database are you actually using? — Justin Cave, Sep 21 '15 at 19:49

score 1 · Accepted Answer · edited May 23 '17 at 11:44

1

Try using this instead:

Update
(
    Select  a.Col1, a.Col2, a.Col3,
            b.Col1 As New_Col1, 
            b.Col2 As New_Col2, 
            b.Col3 As New_Col3
    From    Table_A     a
    Join    Table_B     b   On  a.ID = b.ID
) ToUpdate
Set ToUpdate.Col1 = ToUpdate.New_Col1,
    ToUpdate.Col2 = ToUpdate.New_Col2,
    ToUpdate.Col3 = ToUpdate.New_Col3

Reference: Update statement with inner join on Oracle

edited May 23 '17 at 11:44

Community

1
1

answered Sep 21 '15 at 19:49

Siyual

16,415
8
44
58

Not working....giving following error ORA-00933: SQL command not properly ended – Qasim0787 Sep 21 '15 at 19:54
@Qasim0787 Updated query – Siyual Sep 21 '15 at 20:03
Thanks Perfect working fine – Qasim0787 Sep 21 '15 at 20:10

How to Update on table with another table

1 Answers1