Re: SQL Updateing a Join

I ended up writing a program... it was quicker. I spend enough time on it
already. But I will definately trying that next time.

On 1/3/06, Billy Rowe <billyrowe@xxxxxxx> wrote:
>
>    This should work if I understand your column names correctly from each
>    table:
>    Update LAWDBF7/DBPOPLI DBPOPLI
>      Set dbpli_closed_fl = 'Y'
>    where dbpopli.dbpli_closed_fl     =  'N'
>     and  dbpopli.dbpli_cancelled_fl  =  'N'
>     and  dbpopli.dbpli_quantity      =  dbpopli.dbpli_approve_qty
>     and  dbpopli.dbpli_rec_qty       =  dbpopli.dbpli_approve_qty
>     and exists
>          ( Select *
>             from LAWDBF7/DBPOPAO
>            where DBPOPLI.DBPLI_COMPANY   =  DBPAO_COMPANY
>             and  DBPOPLI.DBPLI_PO_NUMBER =  DBPAO_PO_NUMBER
>             and  DBPOPLI.DBPLI_LINE_NBR  =  DBPAO_LINE_NBR )
>
>    Mike Wills wrote:
>
> I have this select stmt:
> SELECT * FROM LAWDBF7/DBPOPLI DBPOPLI, LAWDBF7/DBPOPAO
> DBPOPAO WHERE DBPOPLI.DBPLI_COMPANY = DBPOPAO.DBPAO_COMPANY AND
> DBPOPLI.DBPLI_PO_NUMBER = DBPOPAO.DBPAO_PO_NUMBER AND
> DBPOPLI.DBPLI_LINE_NBR = DBPOPAO.DBPAO_LINE_NBR AND ( (
> DBPLI_CLOSED_FL = 'N' AND DBPLI_CANCELLED_FL = 'N') AND
> DBPLI_QUANTITY = DBPLI_APPROVE_QTY AND DBPLI_REC_QTY =
> DBPLI_APPROVE_QTY)
>
> I am want to update a field in DBPOPLI without the join it would be
> something like
> UPDATE MNWTEST/DBPOPLI SET DBPLI_CLOSED_FL = 'Y'
>      WHERE ((DBPLI_CLOSED_FL = 'N' AND
>              DBPLI_CANCELLED_FL = 'N') AND
>              DBPLI_QUANTITY = DBPLI_APPROVE_QTY AND
>              DBPLI_REC_QTY =DBPLI_APPROVE_QTY)
>
> I found some help on a site on how to do this with DB2 here
> (http://www.devx.com/tips/Tip/14577
> ) however, it appears like iSeries doesn't support this method of updating
> records. (I know iSeries isn't technically DB2, but generally it has
> worked
> in the past). We are on V5R3 now. How would I do this short of a simple
> program which I am about ready to write.
>
> --
> Mike Wills
> koldark@xxxxxxxxx
> http://mikewills.name
> Want Gmail? Email koldark+gmail@xxxxxxxxx to get on my waiting list.
>
> "There are 10 types of people in the world: those who understand binary,
> and
> those who don't." -Unknown
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>


--
Mike Wills
koldark@xxxxxxxxx
http://mikewills.name
Want Gmail? Email koldark+gmail@xxxxxxxxx to get on my waiting list.

"There are 10 types of people in the world: those who understand binary, and
those who don't." -Unknown