× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.