Geek asks and answers

DELETE record in relational position in MySQL?

I am trying to clear records stored in a MySQL table. If the line contains %X%, I need to delete this line and the line immediately below it, regardless of the contents. For instance. (sorry if the table offends any 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 just a couple of queries? Or I will have to first execute a SELECT query (using the search parameter %X%), then execute these results and execute DELETE ... WHERE for each index returned + 1

+5
source share
4 answers

, (, LIKE, (. )        table. db   idcol IN   (SELECT idcol FROM db. table WHERE col LIKE '% X%')    idcol IN ( SELECT idcol +1 FROM db . table WHERE col `LIKE '% X%') ​​

+2

, test id data.

SELECT, , ( id id ):

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 )

3 6. 2 5 %X%, good.

, %X%, , 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%'
)

3, 6, 2, 5 - !

MySQL, , , , :

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);

... 1, 4 7 test!

( <, ORDER BY LIMIT, , .)

+1

DELETE:

, " " ID INT, MySQL , :

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 ( , )

, , LEFT JOIN , , 1. "" , DELETE:

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%'

:

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

JOIN - DELETE, , , "" ID, NEXTID.

+1

In one request, there is no sensible way to do this. (Perhaps this is possible, but the query you need to use will be unreasonably complex and will almost certainly not be portable for other SQL engines.)

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

0
source

More articles:


All Articles