Assumed you want to update the first record of a list of records with a
duplicate palet no and duplicate date. (you may even add a where condition
to only filter a specific date:
Here is another solution:
Update yourtable x
set MyDate = '2013-07-18'
where rrn(x) in (Select RRNa
from (Select Palet, myDate, min(rrn(a)) RRNA
from yourtable a
--****** where myDate = '2013-07-19'
group by Palet, Mydate
having count(*) > 1) b);
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: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Nicholas T Lawrence
Gesendet: Tuesday, 23.7 2013 23:34
An: rpg400-l@xxxxxxxxxxxx
Betreff: RE: SQL Update duplicate records
It seems to me that the previously mentioned query may have difficulties
when there are more that two records that are duplicates of each other.
This example might help:
CREATE TABLE table1 (col1 int);
-- numbers indicate how many duplicate rows INSERT INTO table1 VALUES (4),
(2), (2), (4), (1), (4), (4);
SELECT RRN(a), a.col1 FROM table1
Results:
1 | 4
2 | 2
3 | 2
4 | 4
5 | 1
6 | 4
7 | 4
-- variation of previous query (returns 3 matching rows for value 4) SELECT
RRN(a), a.col1 FROM table1 a WHERE RRN(a) IN
(SELECT RRN(b) FROM table1 b, table1 c WHERE b.col1 = c.col1 and RRN(b)
RRN(c) AND b.col1 = 4);
Results:
4 | 4
6 | 4
7 | 4
-- exception join query (which could be further simplified), in other words
- exclude the rows that are matched with rows in C
SELECT RRN(b), b.col1
FROM table1 b
EXCEPTION JOIN table1 c ON (b.col1 = c.col1 AND RRN(c) < RRN(b) )
WHERE b.col1 = 4;
Results
1 | 4
This kind of query could then be used as part of the where clause of your
update.
An alternative here might be to use an SQL cursor for a simpler query that
includes the duplicates - and then perform an "update where current", once
you've fetched the row that really needs to be updated.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Fr
bafycurs5a.htm
In general, the relational model (highly) discourages duplicate rows in a
table, which one of the reasons why duplicate rows are so hard to work with
SQL. If you do not have a column or columns that make a primary key for
your data, consider adding an identity column and using that as a primary
key - then DB2 will generate a unique value during an SQL INSERT.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Fr
bafysqlpidentity.htm
IBM STG Lab Services offers extensive training on SQL Mike Cain just
recently blogged about some of the advanced SQL Courses that are available
http://db2fori.blogspot.com/2013/06/advancing-your-sql-knowledge-and-skills.
html
.
Nick Lawrence
DB2 for IBM i
Success is not final, failure is not fatal: it is the courage to continue
that counts.
- Winston Churchill
--
This is the RPG programming on the IBM i (AS/400 and 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.
midrange.com
