Re: Update SQL Statment

["Wes" <small_du@xxxxxxxxx>]

Thanks Rob. I was hopping there would be a cleaner way of doing it.

Wes
<rob@xxxxxxxxx> wrote in message
news:OFC77A4DD2.4F0E5FE4-ON05256F38.0075ACA1-05256F38.00775CBC@xxxxxxxxxx
> SELECT * FROM ROB/WESTITLES
> ....+....1....+....2..
> TITLE_ID    YTD_SALES
> BOOK1          100.00
> BOOK2          200.00
> BOOK3          300.00
>
> SELECT * FROM ROB/WESSALES
> ....+....1....+....2....+....3....
> TITLE_ID          QTY   ORD_DATE
> BOOK1               1   2004-10-25
> BOOK1               2   2004-10-25
> BOOK1               7   2004-10-24
> BOOK2               4   2004-10-24
> BOOK2               9   2004-10-25
>
>
> UPDATE westitles t
>     SET t.ytd_sales=t.ytd_sales +
>          (SELECT sum(s.qty) FROM wessales s
>          WHERE s.title_id = t.title_id
>            and s.ord_date =
>                (select max(s.ord_date) from wessales s)
>          )
>     WHERE t.title_id IN
>          (SELECT s.title_id FROM wessales s INNER JOIN westitles t
>          ON s.title_id = t.title_id AND s.ord_date =
>            (select max(s.ord_date) from wessales s)
>          )
>
> SELECT * FROM ROB/WESTITLES
> ....+....1....+....2..
> TITLE_ID    YTD_SALES
> BOOK1          103.00
> BOOK2          209.00
> BOOK3          300.00
>
> Could be cleaned up a little perhaps.  But it definitely works.
>
>
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Wes" <small_du@xxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 10/25/2004 04:11 PM
> Please respond to
> RPG programming on the AS400 / iSeries
<rpg400-l@xxxxxxxxxxxx>
>
>
> To
> rpg400-l@xxxxxxxxxxxx
> cc
>
> Fax to
>
> Subject
> Update SQL Statment
>
>
>
>
>
>
> Is there support for join update statement in AS/400 just like MS SQL
>
> E.g.
>
> UPDATE titles
>     SET t.ytd_sales = t.ytd_sales + s.qty
>     FROM titles t, sales s
>     WHERE t.title_id = s.title_id
>     AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
> Wes
>
>
>
> --
> 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.
>
>
> --
> 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.
>
>