1

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.

jabal
  • 11,987
  • 12
  • 51
  • 99

2 Answers2

1

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)

Denys Kniazhev-Support Ukraine
  • 8,862
  • 7
  • 47
  • 63
0

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

Community
  • 1
  • 1
Gidil
  • 4,137
  • 2
  • 34
  • 50
  • 1
    Ok, `SELECT max( a.delta ) FROM mytable a JOIN mytable b ON ( a.nid = b.nid AND a.vid = b.vid AND a.field_value = b.field_value )` yields 7. But how to use this in the delete? – jabal Oct 14 '12 at 18:04