DB2 SQL Update

I am trying to update a table (DB2) while joining to a second table, but getting "Keyword JOIN not expected". Can someone suggest proper syntax? Thank you.

update testdta/f060120 es set es.yajbcx='test'
join testdta/f060116 em
on em.yaan8=es.yaan8
where em.yapst<>0

wooi,

The join criteria will have to be moved to the where clause as you can't update joined tables.

It would be something like (not sure of DB2 syntax):

update testdta/f060120 es
set es.yajbcx='test'
where em.yaan8 in (select es.yaan8 from testdta/f060116 em where es.yaan8=em.yaan8)
and em.yapst<>0

To update a table while joining to a second table, read techniques at:
http://www.itjungle.com/mgo/mgo100303-story02.html

http://www.itjungle.com/fhg/fhg042711-story02.html
http://www.itjungle.com/fhg/fhg041410-story02.html

Re: RE: DB2 SQL Update

This is one place where I think the ANSI SQL standard is *severely* lacking. But if your version of DB2 is recent enough, you might be able to get by with a "MERGE" statement.

FWIW, SQL Server has a non-standard, but beautifully elegant way of making these updates work.

I'm a strong advocate of this method and think ANSI should adopt it full heartedly. Check out this example: http://technet.microsoft.com/en-us/library/ms177523.aspx#OtherTables

(Note: You can use a table alias, as in UPDATE TableAlias FROM DTA.F0101 TableAlias ... This gives you the flexibility to update your intended target even in the case of self-joins)