Re: Need an SQL update lesson

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Man, that is lucky if it worked!! And I'm very glad if it did!

Cheers
Vern

On 7/17/2018 3:25 PM, Jeff Crosby wrote:
> I think Vern's worked.  Further testing after vacation.
>
> Thanks all.  This list is so great.
>
> On Tue, Jul 17, 2018 at 4:24 PM, Jim It <jim_it@xxxxxxxxxxx> wrote:
>
> > Jeff,
> >
> > 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');
> >
> > Becomes
> >
> > Merge Into JEFF.ORDTL as TGT
> > Using
> > (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')) As SRC
> > On TGT.ONRCU = SRC.ONRCU
> > When Matched Then Set TGT.CWSTS = '3'
> >
> > Jim
> > ________________________________
> > From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Jeff
> > Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
> > Sent: Tuesday, July 17, 2018 4:05 PM
> > To: Midrange Systems Technical Discussion
> > Subject: Need an SQL update lesson
> >
> > 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.
> >
> >
> > --
> >
> >
> > Jeff Crosby
> > VP Information Systems
> > UniPro FoodService/Dilgard
> > P.O. Box 13369
> > Ft. Wayne, IN 46868-3369
> > 260-422-7531
> > direct.dilgardfoods.com
> >
> > The opinions expressed are my own and not necessarily the opinion of my
> > company.  Unless I say so.
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: http://amzn.to/2dEadiD
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: http://amzn.to/2dEadiD