CODEWITHSUNDEEP
sqloracle-database
newSqlz
newSqlz

Reputation: 137

How to delete rows from table, based on two columns of same table data?

I have table with attribute change history, that looks like:

| id     | attr     | value  | version |
|--------|----------|--------|---------|
| UUID-1 | name     | chuck  | 1       |
| UUID-1 | password | morris | 1       |
| UUID-1 | name     | chack  | 0       |
| UUID-2 | name     | aron   | 2       |
| UUID-2 | name     | alan   | 1       |
| UUID-2 | name     | agor   | 0       |

Now I have to drop old data for every 'id' (rows with MAX(version) should stay), and I want to get:

| id     | attr     | value  | version |
|--------|----------|--------|---------|
| UUID-1 | name     | chuck  | 1       |
| UUID-1 | password | morris | 1       |
| UUID-2 | name     | aron   | 2       |

So, I've decided that i can obtain this with such construction:

DELETE
FROM
    table1 pch
WHERE
    EXISTS (
    SELECT
        id, MAX(version) AS maxVer
    FROM
        table1 pch2
    WHERE
        pch.id = pch2.id AND pch.version != maxVer
    GROUP BY
        id)

or something like this, but... oracle sql can't work with aliases in 'where/having' clauses.

any options to do this thing another way?

Upvotes: 0

Views: 842

Answers (4)

William Robertson
William Robertson

Reputation: 15991

The one I always use is:

delete table1 t1
where  t1.rowid in
        ( select lag(rowid) over (partition by id, attr order by version)
          from   table1 );

This deletes every previous row in order of version, partitioned by ID and ATTR.

UUID-1 has two rows with version = 1. I have retained the max row per ID + ATTR combination to match your expected results.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Just use a correlated subquery instead of group by:

DELETE FROM table1 pch
WHERE pch.version < (SELECT MAX(version) AS maxVer
                     FROM table1 pch2
                     WHERE pch.id = pch2.id 
                    );

Upvotes: 1

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

Here's a possible solution.

delete from history where rowid in
  (select history.rowid from history join
    (select ID, ATTR, max(version) version from history group by ID, ATTR) t
      on history.ID = t.ID and history.ATTR = t.ATTR and history.VERSION <> t.version);

First, we find rows with max versions that should not be deleted.
Then we select rowids of those rows that have the same ID and ATTR, but different VERSION (the 'old' rows).
And finally we remove those rows by rowids.

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

There are a lot of different variants... Just one of them:

delete from t
where 
t.rowid in 
(
select v.rowid 
from (select
       t1.rowid, 
       row_number()over(partition by t1.id,t1.attr order by version desc) rn
      from t t1
    ) v
where rn>1
);

Upvotes: 0

Related Questions