CODEWITHSUNDEEP
mysqlsqldrupaldelete-row
jabal
jabal

Reputation: 12347

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.

Upvotes: 1

Views: 317

Answers (2)

Denys Kniazhev-Support Ukraine
Denys Kniazhev-Support Ukraine

Reputation: 9102

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)

Upvotes: 1

Gidil
Gidil

Reputation: 4137

Join the table to itself on three columns (NID, VID, FIELD_VALUE) and SELECT the MAX value for DELTA.

This will work as long as you don't have any other columns involved.

Here is a good example for this

Upvotes: 0

Related Questions