I've managed to cobble together a query to remove duplicates from an MVS DB. I can identify them, but my brain has been fried by getting this far and I'm missing the final step... can anyone put me right...

This is what I have:

select * from
--delete from
(SELECT ROW_NUMBER() OVER (partition by col1, col2, col3 order by col1)
FROM MYDB.PTB WHERE VALID = 'Y' AND PLATFORM = 'AIX' and DATA='ABC')
as E(RN) where RN>1

I was hoping it would be as simple as changing select * from to delete from.. but that doesn't work.

Thanks in advance..

Extra info:

Database server = DB2 z/OS 9.1.5
Using QMF for Windows 8.1 FP 10 to connect to it
Running on Windows 7

The error is not particularly helpful

[IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: "<IDENTIFIER>". SQLSTATE=42601

so in otherwords, syntax is wrong

Please read this carefully, especially 1) and 2)
Must Read before posting
and supply more information.

sorry, didn't realize this was needed as it's an sql syntax problem.

I've updated my original post

I found another similar hit which said to use this with z/OS 9.1:

but when I use my own version of this:

DELETE FROM MYDB.PTB
WHERE RID(MYDB.PTB)
IN (SELECT rid
FROM (SELECT RID(MYDB.PTB) rid
, ROWNUMBER() OVER(PARTITION BY col1, col2, col3) rn
FROM MYDB.PTB where VALID = 'Y' AND PLATFORM = 'AIX' and data = 'ABC')
WHERE rn > 1 )

I get another error:

An error occurred while running the query.

[IBM][CLI Driver][DB2] SQL0104N An unexpected token "<EMPTY>" was found following "". Expected tokens may include: "CORRELATION NAME". SQLSTATE=42601

or the same in QMF on MVS:
SQL error at or before <EMPTY>

or in Command Editor (DB2 Connect for Windows 9.5 FP1)
SQL0104N An unexpected token "<EMPTY>" was found following "". Expected tokens may include: "CORRELATION NAME". SQLSTATE=42601

SQL0104N An unexpected token "<EMPTY>" was found following "". Expected tokens may include: "CORRELATION NAME".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

Add the correlation name after the innermost subselect, as the error message suggests.

please can you give me a better clue?

I changed it to :

... 'ABC') as XX

and got:

[IBM][CLI Driver][DB2] SQL0171N The data type, length or value of argument "1" of routine "RID" is incorrect. SQLSTATE=42815

with:

... 'ABC') as XX(rn)

[IBM][CLI Driver][DB2] SQL0158N The number of columns specified for "" is not the same as the number of columns in the result table. SQLSTATE=42811

and:

... 'ABC') as XX(dum, rn)

[IBM][CLI Driver][DB2] SQL0171N The data type, length or value of argument "1" of routine "RID" is incorrect. SQLSTATE=42815

I think I have it working...

DELETE FROM HLQ.TABLENAME
WHERE RID(HLQ.TABLENAME) IN
(SELECT RID FROM (
SELECT RID(HLQ.TABLENAME) AS RID,
ROWNUMBER() OVER(PARTITION BY field1, field2, field3)
-- field 1 +2 +3 is unique
FROM HLQ.TABLENAME WHERE field1 = 'X' AND
field2 = 'Y' AND field3='Z') AS E(RID,RN)
WHERE RN > 1)