Using Join in delete query

Question

I have to write a query to delete duplicate active entries from the table. Here is a sample set of rows from the table.

select * from balance where ACCOUNT = '832076635';

ACCOUNT    BALANCE_AMT  TIMESTAMP  ACTIVE    
832076635  10.23        02-MAR-18  1   
832076635  13.34        29-DEC-17  1
832076635  9.22         01-OCT-17  0

I have to delete the row with max(timestamp) with active = 1. There could be several such rows. I tried the below query but it does not work. Could someone please help.

WITH TMP AS
  (select account, max(timestamp) AS MAXTIME from balance 
   where active = 1 group by account   having count(*) > 1)
delete from balance b 
  INNER JOIN TMP t
  ON (b.account = t.account
     AND b.timestamp = t.MAXTIME)

Please don't post images of data or code. Instead, learn to use the markdown editor. I've done it this time. Thanks. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented May 10, 2018 at 11:23

MT0 · Accepted Answer · 2018-05-10 11:38:47Z

You can use a MERGE statement to delete all the duplicate rows other than the oldest:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE balance ( account, timestamp, active ) AS
SELECT 832076635, DATE '2018-03-02', 1 FROM DUAL UNION ALL
SELECT 832076635, DATE '2017-12-29', 1 FROM DUAL UNION ALL
SELECT 832076635, DATE '2017-10-01', 0 FROM DUAL;

Query 1:

MERGE INTO balance src
USING (
  SELECT RID
  FROM   (
    SELECT ROWID AS rid,
           ROW_NUMBER() OVER ( PARTITION BY account ORDER BY timestamp ASC ) AS rn
    FROM   balance
    WHERE  active = 1
  )
  WHERE rn > 1
) dst
ON ( src.ROWID = dst.RID )
WHEN MATCHED THEN
  UPDATE SET active = 1
  DELETE WHERE 1 = 1

Query 2:

SELECT * FROM BALANCE

Results:

|   ACCOUNT |            TIMESTAMP | ACTIVE |
|-----------|----------------------|--------|
| 832076635 | 2017-12-29T00:00:00Z |      1 |
| 832076635 | 2017-10-01T00:00:00Z |      0 |

Or this would just delete the newest active duplicate:

MERGE INTO balance src
USING (
  SELECT RID
  FROM   (
    SELECT ROWID AS rid,
           ROW_NUMBER() OVER ( PARTITION BY account ORDER BY timestamp DESC ) AS rn,
           COUNT(*) OVER ( PARTITION BY account ) AS ct
    FROM   balance
    WHERE  active = 1
  )
  WHERE rn = 1 AND ct > 1
) dst
ON ( src.ROWID = dst.RID )
WHEN MATCHED THEN
  UPDATE SET active = 1
  DELETE WHERE 1 = 1

Bob Jarvis - Слава Україні · Accepted Answer · 2018-05-10 11:40:13Z

0

Oracle doesn't support common table expressions or joins in anything but a SELECT statement, but you can use a correlated subquery to do the same job:

DELETE FROM BALANCE b
  WHERE EXISTS (SELECT t.ACCOUNT, MAX(t.TIMESTAMP)
                  FROM BALANCE t
                  WHERE t.ACTIVE = 1
                  GROUP BY t.ACCOUNT
                  HAVING COUNT(*) > 1 AND
                         t.ACCOUNT = b.ACCOUNT AND
                         MAX(t.TIMESTAMP) = b.TIMESTAMP AND
                         b.ACTIVE = 1);

Also, I suggest that you shouldn't use reserved words such as TIMESTAMP as a column name. It will eventually bite you.

Best of luck.

edited May 10, 2018 at 11:40

answered May 10, 2018 at 11:33

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

Using Join in delete query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related