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%2Frbafycurs5a.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%2Frbafysqlpidentity.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