Error while executing DELETE statement

[d7d1cd_]

There is a table T1, which contains records with the same value of one field F1 (we will call such records duplicates). To find out how many such duplicates I write a query:

select F1, count(*) CNT
from T1
group by F1
having count(*) > 1

The table also has a numeric field F2. Task: remove all duplicates from the table, leaving only those with the smallest F2 among its group. In this case, among the smallest there may be more than one record. In this case, you must leave any.

I found an example on this resource, however it doesn't work. An error occurs during execution: "Token ( was not valid. Valids tokens: <IDENTIFIERS>". Why does the error occur?

delete from
     (select rownumber() over(partition by F1 order by F1, F2)
       from T1) AS E(RN)
where RN> 1

[vazymimil]

The documentation you refer to concerns DB2LUW 11.5. You have to refer to https://www.ibm.com/support/knowledg...rbafzdelst.htm. By the way even DB2LUW 11.5 would not execute your statement because it contains a group by that makes it read only.

Probalbly this would do the job (didn't test):

delete from t1 where (select count(1) from t1 t1gb where t1gb.f1 = t1.f1 group by f1) > 1

[d7d1cd_]

Your example doesn't work. More precisely, it works too well: it removes everything! Here is a link to the test code.

Further, why did you decide that if there is a grouping, then the data cannot be deleted? After all, the data is grouped only for numbering. Here's an example where everything works out. Moreover, the database is DB2.

[TedHolt]

Here are a couple of ways you might delete the duplicates and keep the one with the lowest sequence number. The first is based on the example. I gave these a quick test, but not a proper test.

declare global temporary table T1
( F1 char (1), F2 dec(3));

insert into session.T1 values
('A', 1),
('A', 2),
('A', 3),
('B', 1),
('C', 5),
('C', 8),
('D', 1),
('D', 2),
('D', 3);

-- this works   
delete from session.T1
 where (F1, F2) in
          (select F1, F2
             from (select x.F1, x.F2,
                          rownumber() over (partition by F1 order by F1, F2) as rn
                     from session.T1 as x)
            where rn > 1);

-- so does this
delete from session.T1
 where (F1, F2) not in
    (select F1, min(F2) as F2
       from session.T1
      group by F1);

[d7d1cd_]

In fact, of course, my table has many more fields. Therefore, using the variant where the fields are listed is not very convenient. Enumeration of only "key" fields is not suitable, since the table may contain records whose values for these fields completely coincide.

Good people suggested the following removal option:

delete from test mt
where rid(mt) in
      (
        select rid  
        from 
        (
          select rid(t) rid,
                 rownumber() over(partition by F1 
                                  order by F1, F2) as RN 
          from test t
        )
        where RN > 1
      )

What do you think, if in this example you replace the RID function with the RRN, this will not affect the result?

[Vectorspace]

Moreover, the database is DB2.

There are many different flavours of DB2, and they are not all the same as each other. DB2LUW is not the same as DB2 on IBM i. That's why there is separate DB2 documentation for each platform.