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

Answer 1

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