MySql: How to Delete rows from a table EXCEPT a row with specific ID

Question

I have an html table with checkboxes. Users are permitted to delete rows from that html table except one specific one, whose value is hard coded in the DB.

If a user accidently checks it regardless, I want my server code (php) or even better the MySqL to run a delete query for all the rows they checked EXCEPT for that 1 specific row. So,something like this( which is wrong):

DELETE FROM table_name
WHERE row_id = some_value
EXCEPT row_id = some_value;

Many thanks !

there's no `except`. use inequality: `where row_id != do_not_delete_this_value` — Marc B, May 28 '15 at 21:46
Better put an if statement, validating the input, and print an error to the user, if he checks the wrong row, instead of adding logics into SQL. — user4035, May 28 '15 at 21:49

score 3 · Answer 1 · answered May 28 '15 at 21:48

3

I think the logic you want is AND:

DELETE FROM table_name
WHERE row_id = some_value AND row_id <> some_value;

Of course, some_value should be different most of the time.

answered May 28 '15 at 21:48

Gordon Linoff

1,242,037
58
646
786

2

It's redundant anyways, `delete ... where row_id=42` would never delete any other row_id to begin with. `where row_id=42 and row_id<>69`? – Marc B May 28 '15 at 21:50
@MarcB . . . The OP is trying to prevent `where row_id = 69`. Admittedly, this could be done at the application level, but as a query:`where row_id = 69 and row_id <> 69` also does the trick. – Gordon Linoff May 29 '15 at 00:53

score 2 · Accepted Answer · answered May 28 '15 at 22:25

2

DELETE FROM table_name WHERE row_id NOT IN (do_not_delete_this_value );

do_not_delete_this_value will be the id of the row you don't want to delete, rest all records will get deleted.

answered May 28 '15 at 22:25

Faiz Akhtar

56
6

Thank you this did it ! – Vince May 28 '15 at 22:45

score 0 · Answer 3 · answered Nov 29 '20 at 13:09

In fact there are a couple of ways to do it, for deleting all row's except one specific ID, type the following command in SQL query:

In between parentheses, type the ID number that you want to keep.

DELETE FROM `table_name` WHERE `ID` NOT IN (1)

Also, to delete all records except some id's, use this command:

DELETE FROM `table_name` WHERE `ID` NOT IN (1,2,6,10)

MySql: How to Delete rows from a table EXCEPT a row with specific ID

3 Answers3