"Billy" wrote:

> I want to update  records using an inner join but cannot work out the syntax.
> Can anyone help me?
>
> They way I am doing the update is thus:
>      Update LTT.SROPRG SET (PGPLAN, PGRESP)= (Select HANDLER as HANDA,
> HANDLER as HANDB from LISTERDAT.HANDLER where PGPRDC = ITEM)
>
> but this way throws an error when the number of items in LISTERDAT.HANDLER
> is less that the number of items in LTT.SROPRG. The error message says null
> values not allowed
>
> I would like to do it like this:
> Update LTT.SROPRG SET PGPLAN = HANDLER, PGRESP = HANDLER Inner Join
> LISTERDAT.HANDLER on PGPRDC = ITEM
>
> but I cannot get the syntax right. Any help much appreciated

"Jens Süßmeyer" wrote:

> Due to the lack of information about the columns that´ll be a sort of
> solution, vene for the syntax:
>
>
> Update LTT.SROPRG SET PGPLAN = HANDLER, PGRESP = HANDLER
> FROM LTT.SROPRG S Inner Join
> LISTERDAT.HANDLER H on S.PGPRDC = H.ITEM
> --
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
>
> "Billy" wrote:
>
> > I want to update  records using an inner join but cannot work out the syntax.
> > Can anyone help me?
> >
> > They way I am doing the update is thus:
> >      Update LTT.SROPRG SET (PGPLAN, PGRESP)= (Select HANDLER as HANDA,
> > HANDLER as HANDB from LISTERDAT.HANDLER where PGPRDC = ITEM)
> >
> > but this way throws an error when the number of items in LISTERDAT.HANDLER
> > is less that the number of items in LTT.SROPRG. The error message says null
> > values not allowed
> >
> > I would like to do it like this:
> > Update LTT.SROPRG SET PGPLAN = HANDLER, PGRESP = HANDLER Inner Join
> > LISTERDAT.HANDLER on PGPRDC = ITEM
> >
> > but I cannot get the syntax right. Any help much appreciated

"Billy" <Bi***@discussions.microsoft.com> wrote in message
news:0F09784C-CA11-4AC9-AFB2-312010A384E8@microsoft.com...
> Thx for the reply.
>
> When I try your solution, I get this error message
>
> [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword FROM not
> expected. Valid tokens: <END-OF-STATEMENT>.
>
> Just so you know, I have posted this in here because it's a SQL statement
> I'm running, but I'm not using the query on a SQL Server - I'm running it
> against an AS400 so it might be (most probably) a limitation on the SQL
> syntax allowed on the AS400...should've mentioned that before, sorry.
>
> Any other ideas?
>
> "Jens Süßmeyer" wrote:
>
> > Due to the lack of information about the columns that´ll be a sort of
> > solution, vene for the syntax:
> >
> >
> > Update LTT.SROPRG SET PGPLAN = HANDLER, PGRESP = HANDLER
> > FROM LTT.SROPRG S Inner Join
> > LISTERDAT.HANDLER H on S.PGPRDC = H.ITEM
> > --
> > HTH, Jens Suessmeyer.
> >
> > ---
> > http://www.sqlserver2005.de
> > ---
> >
> >
> > "Billy" wrote:
> >
> > > I want to update  records using an inner join but cannot work out the
syntax.
> > > Can anyone help me?
> > >
> > > They way I am doing the update is thus:
> > >      Update LTT.SROPRG SET (PGPLAN, PGRESP)= (Select HANDLER as HANDA,
> > > HANDLER as HANDB from LISTERDAT.HANDLER where PGPRDC = ITEM)
> > >
> > > but this way throws an error when the number of items in
LISTERDAT.HANDLER
> > > is less that the number of items in LTT.SROPRG. The error message says
null
> > > values not allowed
> > >
> > > I would like to do it like this:
> > > Update LTT.SROPRG SET PGPLAN = HANDLER, PGRESP = HANDLER Inner Join
> > > LISTERDAT.HANDLER on PGPRDC = ITEM
> > >
> > > but I cannot get the syntax right. Any help much appreciated

"Daniel Wilson" wrote:

> I have tried the UPDATE FROM syntax (a favorite of mine) against DB2 on an
> AS400 and also found it not to work there.
>
> My primary reference on DB2's syntax is
> http://publib.boulder.ibm.com/iseries/legacy/html/as400/v4r5/ic2924/index.htm?info/db2/rbafymst02.htm
>
> Not sure what newsgroups are out there for DB2 ... Jens' answer was
> beautiful for MS SQL Server ... can't comment further on DB2.
>
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
> "Billy" <Bi***@discussions.microsoft.com> wrote in message
> news:0F09784C-CA11-4AC9-AFB2-312010A384E8@microsoft.com...
> > Thx for the reply.
> >
> > When I try your solution, I get this error message
> >
> > [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword FROM not
> > expected. Valid tokens: <END-OF-STATEMENT>.
> >
> > Just so you know, I have posted this in here because it's a SQL statement
> > I'm running, but I'm not using the query on a SQL Server - I'm running it
> > against an AS400 so it might be (most probably) a limitation on the SQL
> > syntax allowed on the AS400...should've mentioned that before, sorry.
> >
> > Any other ideas?
> >
> > "Jens Süßmeyer" wrote:
> >
> > > Due to the lack of information about the columns that´ll be a sort of
> > > solution, vene for the syntax:
> > >
> > >
> > > Update LTT.SROPRG SET PGPLAN = HANDLER, PGRESP = HANDLER
> > > FROM LTT.SROPRG S Inner Join
> > > LISTERDAT.HANDLER H on S.PGPRDC = H.ITEM
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > ---
> > > http://www.sqlserver2005.de
> > > ---
> > >
> > >
> > > "Billy" wrote:
> > >
> > > > I want to update  records using an inner join but cannot work out the
> syntax.
> > > > Can anyone help me?
> > > >
> > > > They way I am doing the update is thus:
> > > >      Update LTT.SROPRG SET (PGPLAN, PGRESP)= (Select HANDLER as HANDA,
> > > > HANDLER as HANDB from LISTERDAT.HANDLER where PGPRDC = ITEM)
> > > >
> > > > but this way throws an error when the number of items in
> LISTERDAT.HANDLER
> > > > is less that the number of items in LTT.SROPRG. The error message says
> null
> > > > values not allowed
> > > >
> > > > I would like to do it like this:
> > > > Update LTT.SROPRG SET PGPLAN = HANDLER, PGRESP = HANDLER Inner Join
> > > > LISTERDAT.HANDLER on PGPRDC = ITEM
> > > >
> > > > but I cannot get the syntax right. Any help much appreciated
>
>
>

>I want to update  records using an inner join but cannot work out the syntax.
>Can anyone help me?
>
>They way I am doing the update is thus:
>     Update LTT.SROPRG SET (PGPLAN, PGRESP)= (Select HANDLER as HANDA,
>HANDLER as HANDB from LISTERDAT.HANDLER where PGPRDC = ITEM)
>
>but this way throws an error when the number of items in LISTERDAT.HANDLER
>is less that the number of items in LTT.SROPRG. The error message says null
>values not allowed
>
>I would like to do it like this:
>Update LTT.SROPRG SET PGPLAN = HANDLER, PGRESP = HANDLER Inner Join
>LISTERDAT.HANDLER on PGPRDC = ITEM
>
>but I cannot get the syntax right. Any help much appreciated