Error when updating table from another table

Question

My statement look like this,

update DATA_SOURCE tgt
 set 
 tgt.DATA_SOURCE_NM=src.DATA_SOURCE_NM,
 tgt.ODS_UPDATE_TS=src.ODS_UPDATE_TS
 select DATA_SOURCE_NM,ODS_UPDATE_TS
 from DATA_SOURCE_BKP src
where tgt.DATA_SRC_ID=src.DATA_SRC_ID;

I got following error message:

error ^ found "SELECT" (at char 103) expecting a keyword

I am using Aginity workbench for netezza. What am I doing wrong?

Answer 1

Try this

update tgt
 set 
 tgt.DATA_SOURCE_NM=src.DATA_SOURCE_NM,
 tgt.ODS_UPDATE_TS=src.ODS_UPDATE_TS
from DATA_SOURCE tgt
inner join DATA_SOURCE_BKP src on tgt.DATA_SRC_ID=src.DATA_SRC_ID;

--where

may be you can add a where clause in the end to filter few more conditions

Answer 2

You should leave the select keyword and do a join after your FROM. I found this solution: https://stackoverflow.com/a/2334741/2377961

in you case:

update DATA_SOURCE tgt
  set 
    tgt.DATA_SOURCE_NM=src.DATA_SOURCE_NM,
    tgt.ODS_UPDATE_TS =src.ODS_UPDATE_TS   

 FROM 
   DATA_SOURCE_BKP src, DATA_SOURCE_BKP tgt
 WHERE 
   tgt.DATA_SRC_ID=src.DATA_SRC_ID

should do it. The FROM part works the same way like:

FROM
    DATA_SOURCE_BKP
INNER JOIN
    DATA_SOURCE_BKP
ON
    DATA_SOURCE_BKP.DATA_SRC_ID = DATA_SOURCE_BKP.DATA_SRC_ID

Answer 3

It works when I remove the line

select DATA_SOURCE_NM,ODS_UPDATE_TS