CODEWITHSUNDEEP
db2sql-update
AlliDeacon
AlliDeacon

Reputation: 1495

Update table based on criteria from a second table

I am trying to update one table based on selection criteria from another table. My SQL is

update
  hshed
set
  oaeiin = 'Y',
  OAEIND = '170201'
from
  hshed
  join cusms on oacono = cmcono
  and oacsno = cmcsno
where
  cmtpid like 'OB10%'
  and oainvd > 180120
  and oaeiin = 'N'

However, I am getting an error that 

 Keyword FROM not expected. Valid tokens: USE SKIP WAIT WITH WHERE.

I am not sure how to update a table based on criteria from a second table, or how to use joins. This is using SQL in a DB2 database.

I have tried searching for a solution with no success.

Any assistance is appreciated.

Upvotes: 1

Views: 150

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

I think the main issue here is that DB2 does not support the update join syntax you are using. One possible workaround might be to use EXISTS clauses to handle the same logic you intend:

UPDATE
    hshed
SET
    oaeiin = 'Y',
    OAEIND = '170201'
WHERE
    EXISTS (SELECT 1 FROM cusms WHERE oacono = cmcono) AND
    EXISTS (SELECT 1 FROM cusms WHERE oacsno = cmcsno) AND
    cmtpid LIKE 'OB10%' AND
    oainvd > 180120 AND
    oaeiin = 'N';

Here is a link to a good Stack Overflow question discussing this problem in general.

Upvotes: 1

Related Questions