Common Table Expressions are always part of an SELECT Statement and NEVER
EVER of an INSERT or UPDATE statement!
Syntax SQL Statement:
WITH ... as (SELECT ....) ... multiple CTEs (optinal)
Select ....
From ...
Where ... (optional)
Group By ... (optional)
Having ... (optional)
UNION or INTERSECT or EXCEPT (Optional)
Select ...
...
Order By ... (Optional)
Select / From / Where / Group By / Having = Sub-Select
Multiple Sub-Selects can be merged by using a UNION, INTERSECT or EXCEPT
clause
The result of the merged sub-selects can be sorted by adding an ORDER BY
clause = Full-Select
The complete SELECT-Statement can additionally include one or multiple CTE.
Syntax for an INSERT Statement (with Select)
INSERT INTO ...
With ... (optional)
Select ...
Syntax for an Update Statement (with Select)
Update ...
Set (FLD1, Fld2, ... FldN) = (Row-Full-Select)
Or
Update ...
Row = Row-Full-Select
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Dennis Lovelady
Gesendet: Wednesday, 03.10 2012 13:57
An: 'Midrange Systems Technical Discussion'
Betreff: RE: Summarizing and fixing up some data
In SQL, there is no way that I know of to delete and update in one
statement.
So here is one solution (I have not tried it), requiring two statements.
NOTE the difference between the T1.RRNBR : RRN(PROBLEMTBL) comparisons in
the UPDATE and the DELETE. This is key to your specs.
WITH T1 AS (SELECT SHIPPER, CONSIGNEE, PO, WAVE, COUNT(*) as COUNTER
, MIN(RRN(PROBLEMTBL)) as RRNBR
FROM PROBLEMTBL
GROUP BY SHIPPER, CONSIGNEE, PO, WAVE
HAVING COUNT(*) > 1
)
UPDATE PROBLEMTBL PT
SET WAVE = 'MULTI'
WHERE EXISTS
(SELECT 1
FROM T1
WHERE T1.SHIPPER = PT.SHIPPER
AND T1.CONSIGNEE = PT.CONSIGNEE
AND T1.PO = PT.PO
AND T1.RRNBR = RRN(PROBLEMTBL)
)
WITH T1 AS (SELECT SHIPPER, CONSIGNEE, PO, WAVE, COUNT(*) as COUNTER
, MIN(RRN(PROBLEMTBL)) as RRNBR
FROM PROBLEMTBL
GROUP BY SHIPPER, CONSIGNEE, PO, WAVE
HAVING COUNT(*) > 1
)
DELETE FROM PROBLEMTBL PT
WHERE EXISTS
(SELECT 1
FROM T1
WHERE T1.SHIPPER = PT.SHIPPER
AND T1.CONSIGNEE = PT.CONSIGNEE
AND T1.PO = PT.PO
AND T1.RRNBR < RRN(PROBLEMTBL)
)
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
The pessimist's ultimate observation:
-- - To make the attempt, is the first step toward faulure.
I have a file that has columns like this:
Shipper, Consignee, PO#, Wave#.
If there is more than one wave# per Shipper/Consignee/PO#, I want to
delete all but one of the records, and change the wave# to 'MULTI'
If there is only one record, leave it alone. Any ideas on how I could
do
this with SQL or RPG?
Thanks.
Jack
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
