Update 1 table from a 2nd table??? 1

[mjcotter]

I have a column on 1 table (entry_filed_dte column) that I need to update with values from the entry_filed_dte column on a second table. The following SQL statement did exactly what I wanted it to do in the SQL Server world. However, it does not work in DB2.

Can anybody help? Thanks!!!

UPDATE tn1odicu
SET tn1odicu.entry_filed_dte = b.entry_filed_dte
FROM tn1odten b
JOIN tn1odicu a
ON a.sail_dte = b.sail_dte
and a.release_dte = b.release_dte
and a.port_code = b.port_code
and a.entry_num = b.entry_num
;

 

[rbod]

UPDATE tn1odicu
SET tn1odicu.entry_filed_dte =
(select b.entry_filed_dte
FROM tn1odten b
JOIN tn1odicu a
ON a.sail_dte = b.sail_dte
and a.release_dte = b.release_dte
and a.port_code = b.port_code
and a.entry_num = b.entry_num)
;

 

[ddiamond]

I'm not sure if DB2 supports the from clause in an update statement. You may have to do it with a sub-query.

UPDATE tn1odicu a
SET tn1odicu.entry_filed_dte = (
  SELECT b.entry_filed_dte
  FROM tn1odten b
  WHERE a.sail_dte = b.sail_dte
    and a.release_dte = b.release_dte
    and a.port_code = b.port_code
    and a.entry_num = b.entry_num)
WHERE EXISTS (    
  SELECT b.entry_filed_dte
  FROM tn1odten b
  WHERE a.sail_dte = b.sail_dte
    and a.release_dte = b.release_dte
    and a.port_code = b.port_code
    and a.entry_num = b.entry_num);

The exists clause is only necessary if tn1odicu has records that are not in tn1odten.

 

[mjcotter]

Thanks ddiamond! Your SQL statement worked perfectly. DB2 had no problem with the from clause in the update statement. Also, that was a good heads up about the exists clause. I definitely needed it b/c the table I was updating had records that did not exist on the other table. Thanks again!!!

rbod, thanks for your reply too! Unfortunately, I kept getting a DB2 syntax error when I tried to run it. I don't think DB2 liked the JOIN and ON statements. I think it would rather see them as a WHERE statement. Though, I could be wrong...

 

[ddiamond]

mjcotter,

In general, DB2 works fine with joins in the from clause. In fact, that is the only way to handle outer joins. I'm not sure, but I think the problem with rbod's sql statement is that unlike sql server, db2 is not smart enough to realize that the tn1odicu in the from clause is the same table that you are trying to update.

 

[Fineitems]

ddiamond - Great post - thanks for saving us a lot of testing time.