Re: Need an SQL update lesson

Hi Jeff

I'm very sure that your example is not IBM i syntax.

Maybe something like this off the top of my head!

Update JEFF.ORDTL d Set CWSTS = '3'
Where d.ONRCU
in (select h.ONRCU
from JEFF.ORHDR h
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1)
and d.CWSTS In('1', '2');

Hope that is even close!

Vern

On 7/17/2018 3:05 PM, Jeff Crosby wrote:

Once again I need help with SQL, this time on an update.

This select:

Select d.ONRCU, d.CWSTS, h.SHPDTISO, c.DATE1
From JEFF.ORDTL d
Inner Join JEFF.ORHDR h On d.ONRCU = h.ONRCU
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1
Where d.CWSTS In('1', '2');

gives me the correct records. Now I want to update field (er, column)
d.CWSTS to a literal based on this select.

I found an example that implies this should work:

Update JEFF.ORDTL Set CWSTS = '3'
From JEFF.ORDTL d
Inner Join JEFF.ORHDR h On d.ONRCU = h.ONRCU
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1
Where d.CWSTS In('1', '2');

but it barfs at the "From" so I assume some different syntax for DB2 on the
IBM i. I feel like I'm this close, but my googling has not resulted in
other examples.

After today I'm on vacation until Tuesday, but I just gotta know . . .

Thanks.