DELETE a record in relational position in MySQL?

Question

I am trying to clean up records stored in a MySQL table. If a row contains %X%, I need to delete that row and the row immediately below it, regardless of content. E.g. (sorry if the table is insulting anyone's intelligence):

| 1 | leave alone 
| 2 | Contains %X% - Delete
| 3 | This row should also be deleted
| 4 | leave alone
| 5 | Contains %X% - Delete
| 6 | This row should also be deleted
| 7 | leave alone

Is there a way to do this using only a couple of queries? Or am I going to have to execute a SELECT query first (using the %x% search parameter) then loop through those results and execute a DELETE...WHERE for each index returned + 1

@Aiias: That's a completely different question. – Mar 17 '13 at 04:34 — , Mar 17 '13 at 04:34

Tucker · Answer 1 · 2013-03-17T05:53:17.400

2

This should work although its a bit clunky (might want to check the LIKE argument as it uses pattern matching (see comments) DELETE FROM table.db WHERE idcol IN ( SELECT idcol FROM db.table WHERE col LIKE '%X%') OR idcolIN ( SELECTidcol+1 FROMdb.tableWHEREcol` LIKE '%X%')

edited Mar 17 '13 at 05:53

answered Mar 17 '13 at 04:40

Tucker

7,017
9
37
55

This relies on not gaps in the ids. – CBroe Mar 17 '13 at 04:53
LIKE doesn't use regexes, REGEXP does; see http://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html#operator_like – ysth Mar 17 '13 at 05:45

score 1 · Answer 2 · answered Mar 17 '13 at 05:47

Let's assume the table was named test and contained to columns named id and data.

We start with a SELECT that gives us the id of all rows that have a preceding row (highest id of all ids lower than id of our current row):

SELECT t1.id FROM test t1
  JOIN test t2 ON
    ( t2.id, true )
      =
    ( SELECT t3.id, t3.data LIKE '%X%' FROM test t3
        WHERE t3.id < t1.id ORDER BY id DESC LIMIT 1 )

That gives us the ids 3 and 6. Their preceding rows 2 and 5 contain %X%, so that's good.

Now lets get the ids of the rows that contain %X% and combine them with the previous ones, via UNION:

(SELECT t1.id FROM test t1
  JOIN test t2 ON
    ( t2.id, true )
      =
    ( SELECT t3.id, t3.data LIKE '%X%' FROM test t3
        WHERE t3.id < t1.id ORDER BY id DESC LIMIT 1 )
)
UNION
(
  SELECT id FROM test WHERE data LIKE '%X%'
)

That gives us 3, 6, 2, 5 - nice!

Now, we can't delete from a table and select from the same table in MySQL - so lets use a temporary table, store our ids that are to be deleted in there, and then read from that temporary table to delete from our original table:

CREATE TEMPORARY TABLE deleteids (id INT);

INSERT INTO deleteids
  (SELECT t1.id FROM test t1
    JOIN test t2 ON
      ( t2.id, true )
        =
      ( SELECT t3.id, t3.data LIKE '%X%' FROM test t3
          WHERE t3.id < t1.id ORDER BY id DESC LIMIT 1 )
  )
  UNION
  (
    SELECT id FROM test WHERE data LIKE '%X%'
  );

  DELETE FROM test WHERE id in (SELECT * FROM deleteids);

... and we are left with the ids 1, 4 and 7 in our test table!

(And since the previous rows are selected using <, ORDER BY and LIMIT, this also works if the ids are not continuous.)

Zane Bien · Answer 3 · 2013-03-17T05:54:33.890

You can do it all in a single DELETE statement:

Assuming the "row immediately after" is based on the order of your INT-based ID column, you can use MySQL variables to assign row numbers which accounts for gaps in your IDs:

DELETE a FROM tbl a
JOIN (
    SELECT a.id, b.id AS nextid
    FROM (
        SELECT     a.id, a.text, @rn:=@rn+1 AS rownum 
        FROM       tbl a
        CROSS JOIN (SELECT @rn:=1) rn_init
        ORDER BY   a.id
    ) a
    LEFT JOIN (
        SELECT     a.id, @rn2:=@rn2+1 AS rownum 
        FROM       tbl a
        CROSS JOIN (SELECT @rn2:=0) rn_init
        ORDER BY   a.id
    ) b ON a.rownum = b.rownum
    WHERE a.text LIKE '%X%'
) b ON a.id IN (b.id, b.nextid)

SQL Fiddle Demo (added additional data for example)

What this does is it first takes your data and ranks it based on your ID column, then we do an offset LEFT JOIN on an almost identical result set except that the rank column is behind by 1. This gets the rows and their immediate "next" rows side by side so that we can pull both of their id's at the same time in the parent DELETE statement:

SELECT a.id, a.text, b.id AS nextid, b.text AS nexttext
FROM (
    SELECT     a.id, a.text, @rn:=@rn+1 AS rownum 
    FROM       tbl a
    CROSS JOIN (SELECT @rn:=1) rn_init
    ORDER BY   a.id
) a
LEFT JOIN (
    SELECT     a.id, a.text, @rn2:=@rn2+1 AS rownum 
    FROM       tbl a
    CROSS JOIN (SELECT @rn2:=0) rn_init
    ORDER BY   a.id
) b ON a.rownum = b.rownum
WHERE a.text LIKE '%X%'

Yields:

ID     | TEXT                   | NEXTID   | NEXTTEXT
2      | Contains %X% - Delete  | 3        | This row should also be deleted
5      | Contains %X% - Delete  | 6        | This row should also be deleted
257    | Contains %X% - Delete  | 3434     | This row should also be deleted
4000   | Contains %X% - Delete  | 4005     | Contains %X% - Delete
4005   | Contains %X% - Delete  | 6000     | Contains %X% - Delete
6000   | Contains %X% - Delete  | 6534     | This row should also be deleted

We then JOIN-DELETE that entire statement on the condition that it deletes rows whose IDs are either the "subselected" ID or NEXTID.

score 0 · Answer 4 · answered Mar 17 '13 at 04:37

There is no reasonable way of doing this in a single query. (It may be possible, but the query you end up having to use will be unreasonably complex, and will almost certainly not be portable to other SQL engines.)

Use the SELECT-then-DELETE approach you described in your question.

DELETE a record in relational position in MySQL?

4 Answers4

SQL Fiddle Demo (added additional data for example)