Search code examples
mysqlsqldrupaldelete-row

How to delete similar rows from my table in MySQL?

I'd like to perform a cleanup in one of my MySQL Drupal tables to remove duplicate values stored. Here is the structure:

NID  VID   DELTA   FIELD_VALUE
100  100   0       foobar
100  100   1       foobar
101  101   0       barbar
101  101   1       barbar
102  102   0       foofoo

My goal is to remove rows with bigger DELTAs if a row with the same NID, VID, FIELD_VALUE exists with smaller DELTA.

My first attempt was the following query:

delete from mytable a where a.delta=1 and 1=(select count(nid) from mytable b where b.nid=a.nid and b.vid=a.vid and b.delta=0 and b.field_value=a.field_value)

Unfortunately the DB says: (MySQL 5.1.65-cll)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a where a.delta=1 and 1 = (select count from `field_value` b where' at line 1

which is not very helpful to me.

UPDATE:

A deleted answer told me that MySQL does not support alias in delete statements, but removing aliases did not help. The subquery is ok, checked separately.

Solution

  • How about this one?

    DELETE a
FROM mytable a
JOIN mytable b ON (a.nid = b.nid
                AND a.vid = b.vid
                AND a.field_value = b.field_value)
WHERE a.delta > b.delta

    (don't forget to backup your data)