How to delete from table but to prevent it from becoming empty?

user_8814612 0
Viewed 52

I have a table with two columns: id (unique key) and data.

I need to delete a row from that table with id equal to some value. Obvious query:

DELETE FROM table WHERE id = value

However, I need to prevent the table from becoming empty (i.e. without rows). In other words, I need to delete the row with id = value only if the table won't become empty after such a deletion.

I need to do it atomically in a single query, I also need to know by the result of this query whether something has been actually deleted or not.

Is it possible?

Thank you.

mysql sql database
3 Answers

You can use simple join for this:

DELETE tbl.* 
FROM tbl 
JOIN tbl tbl1 ON tbl.id <> tbl1.id WHERE tbl.id = 1;

In this case record deleted only exists one more record with different id

Try SQL in sandbox

user_33993560

Looks like this one works:

START TRANSACTION;
DELETE FROM table WHERE id = ... AND (SELECT * FROM (SELECT COUNT(*) FROM table) Temp) > 1;
SELECT ROW_COUNT();

Anyway, might be useful for someone.

user_88146120

Alternatively, one can use a stored routine.

CREATE PROCEDURE delete_keep_one(pID INT)
BEGIN
   DECLARE cnt INT;
   START TRANSACTION;
   DELETE FROM t1 WHERE id = pID;
   SELECT COUNT(*) INTO cnt FROM t1;
   IF cnt = 0 THEN
       ROLLBACK;
   ELSE
       COMMIT;
   end if;
END;


CALL delete_keep_one(200);

So the COMMIT will happen only when COUNT(*) returns greater than 0.

If you need the CALL within another transaction, you take out the COMMIT and or add a parameter which can optionally commit.

user_1836310
Related