Re: Update SQL Statment

By the way, dropping the second WHERE clause might have a tendency to zero 
out the ytd_sales for BOOK3.  Since I defined my files with sql my bacon 
was saved.
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)
         ) 
Null values not allowed in column or variable YTD_SALES.


Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





rob@xxxxxxxxx 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/25/2004 04:43 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
Re: Update SQL Statment






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.