Delete duplicates in database

Question

I have a database that looks like this:

upID | bsID | pID | upUren | upDatum
-----+------+-----+--------+----------
1    | 4    | 2   | 12     | 2011-01-26 00:00:00
2      4      2   | 12     | 2011-01-26 00:00:00
3      4      2   | 12     | 2011-01-27 00:00:00
4      4      2   | 12     | 2011-01-28 00:00:00

I want to delete every row where pID is equal AND bsID is equal AND upDatum is equal. So far i've got this:

DELETE FROM twh_uren_prognose WHERE EXISTS (SELECT b.* FROM twh_uren_prognose b WHERE twh_uren_prognose.upDatum = b.upDatum and twh_uren_prognose.upID <> b.upID

But this gives me this error:

An exception occurred while executing

   DELETE FROM twh_uren_prognose 
   WHERE EXISTS 
             (
               SELECT b.* FROM twh_uren_prognose b 
               WHERE twh_uren_prognose.upDatum = b.upDatum 
               and twh_uren_prognose.upID <> b.upID
              )

SQLSTATE[HY000]: General error: 1093 You can't specify target table 'twh_uren_prognose' for update in FROM clause`.

You want delete all the rows with same bsID , pID,upDatum are equal or just remove te duplciatedc rows and leave a single row? — ScaisEdge, Feb 27 '18 at 08:05
@scaisEdge Yes i want to delete all the rows with same bsID, pID, and upDatum and leave one row of the duplicates. — Gobbin, Feb 27 '18 at 08:06

rakwaht · Answer 1 · 2018-02-27T08:33:51.670

1

I would try using a GROUP BY query:

DELETE FROM twh_uren_prognose WHERE upID NOT IN (
   SELECT temp.myID(
      SELECT MIN(upID) AS myID FROM twh_uren_prognose GROUP BY pID, bsID, upDatum);
   ) AS temp
)

According to this answer this query could solve your problem

delete from twh_uren_prognose where updId NOT IN ( select t.my_id from ( select min(upID ) my_id from twh_uren_prognose group by bsID, pID , upDatum ) t )

edited Feb 27 '18 at 08:33

answered Feb 27 '18 at 07:59

rakwaht

3,666
3
28
45

This query gives me the same error: An exception occurred while executing 'DELETE FROM twh_uren_prognose WHERE upID NOT IN (SELECT MIN(upID) FROM twh_uren_prognose GROUP BY pID, bsID, upDatum)': SQLSTATE[HY000]: General error: 1093 You can't specify target table 'twh_uren_prognose' for update in FROM clause – Gobbin Feb 27 '18 at 08:02
You are near .. you should add an alias .MIN(upID) upID. in your subselect otherwise the outer select cant select upID .. do it so i can upvote – ScaisEdge Feb 27 '18 at 08:28
@scaisEdge is this correct? Unfortunately I cannot run it on anything so this may contains syntax errors – rakwaht Feb 27 '18 at 08:34

ScaisEdge · Accepted Answer · 2018-02-27T08:16:23.750

1

if you " want to delete all the rows with same bsID, pID, and upDatum and leave one row of the duplicates."

you could use a NOT in select for the min upId group by the column you want compare

delete from twh_uren_prognose 
where  updId NOT IN ( select t.my_id from (
    select min(upID ) my_id
    from twh_uren_prognose 
    group by bsID, pID , upDatum ) t
)

edited Feb 27 '18 at 08:16

answered Feb 27 '18 at 08:10

ScaisEdge

131,976
10
91
107

This is exactly the same query as @rakwaht posted. Gives me the same error as that: An exception occurred while executing 'delete from twh_uren_prognose where upID NOT IN ( select min(upID ) from twh_uren_prognose group by bsID, pID , upDatum ) ': SQLSTATE[HY000]: General error: 1093 You can't specify target table 'twh_uren_prognose' for update in FROM clause – Gobbin Feb 27 '18 at 08:13
answer updated .. adding the creation a temporary table based on subselect .. let me know – ScaisEdge Feb 27 '18 at 08:16
It's not a temporay table but an alias. – Daniel E. Feb 27 '18 at 08:57
@DanielE. correct but this reference force the dbengine to buil a temporary table ..(is not a pure mapping between column or table name as are normally the alias ) and is for this "copied values " that is possible delete the rows of the "same" table .. – ScaisEdge Feb 27 '18 at 09:02

Daniel E. · Answer 3 · 2018-02-27T08:41:23.050

1

You just need to specify wich table you are deleting on and it's better to use a left join like this :

 DELETE t1
 FROM twh_uren_prognose t1
 LEFT JOIN 
    twh_uren_prognose t2 ON t1.upDatum = t2.upDatum 
 WHERE t1.upID <> t2.upID

If you want to keep 1 row, let's say the min Id here is what you should do :

 DELETE t1
 FROM twh_uren_prognose t1
 LEFT JOIN 
    twh_uren_prognose t2 ON t1.upDatum = t2.upDatum 
 WHERE t1.upID > t2.upID AND t1.column1 = t2.column1 etc...

edited Feb 27 '18 at 08:41

answered Feb 27 '18 at 08:11

Daniel E.

2,440
1
14
24

This does indeed remove duplicates, is my version of this answer also viable? ---> DELETE t1 FROM twh_uren_prognose t1 LEFT JOIN twh_uren_prognose t2 ON t1.upDatum = t2.upDatum WHERE t1.upID <> t2.upID AND t1.bsID = t2.bsID AND t1.pID = t2.pID – Gobbin Feb 27 '18 at 08:16
I have edited, if you want to keep 1 row. I really think my answer is better than scaisEdge's for performance reasons. – Daniel E. Feb 27 '18 at 08:43
If i run your second query it deletes all the rows, it doesn't leave one row. I don't really care much about performance. – Gobbin Feb 27 '18 at 08:52
It shouldn't in my second query what did you write exactly ? – Daniel E. Feb 27 '18 at 08:53
My bad, i did the comparisation wrong: You said: WHERE t1.upID > t2.upID. I did: WHERE t1.upID <> t2.upID. This also works. You're right – Gobbin Feb 27 '18 at 08:55

score -1 · Answer 4 · answered Feb 27 '18 at 08:31

-1

Please try this one,

DELETE FROM twh_uren_prognose 
WHERE pID = bsID AND pID =upDatum;

This will do your work. There is no need for the complex query

answered Feb 27 '18 at 08:31

Jordy S Chemparathy

539
18
43

How is this going to work? My upDatum field is a DATETIME as you have already seen i think. The fact that both of those are called ID's suggests that they are INTEGERS. So this comparisation is never working. Sorry – Gobbin Feb 27 '18 at 08:33

Delete duplicates in database

4 Answers4