I want to leave always one record if table record count = 1 with SQL

Question

I can delete record with this SQL clause

DELETE FROM TABLE WHERE ID = 2

Anyway i want to leave always one record if table count = 1 even if "ID=2". Thank you for help already now.

Answer 1

Untested, but something in the lines of this might work?

DELETE FROM TABLE WHERE ID = 2 LIMIT (SELECT COUNT(*)-1 FROM TABLE WHERE ID=2);

Maybe add in an if-statement to ensure count is above 1.

Answer 2

Simple way is to disallow any delete that empties the table

CREATE TRIGGER TRG_MyTable_D FOR DELETE
AS
IF NOT EXISTS (SELECT * FROM MyTable)
    ROLLBACK TRAN
GO

More complex, what if you do this multirow delete that empties the table?

DELETE FROM TABLE WHERE ID BETWEEN 2 AND 5

so, randomly repopulate from what you just deleted

CREATE TRIGGER TRG_MyTable_D FOR DELETE
AS
IF NOT EXISTS (SELECT * FROM MyTable)
    INSERT mytable (col2, col2, ..., coln)
    SELECT TOP 1 col2, col2, ..., coln FROM INSERTED --ORDER BY ??

GO

However, the requirement is a bit dangerous and vague. In English, OK, "always have at least one row in the table", but in practice "which row?"

Answer 3

Add a WHERE clause to ensure there's more than one row:

DELETE FROM TABLE 
WHERE ID = 2
AND (SELECT COUNT(*) FROM TABLE) > 1