Clever Geek Handbook

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

I can delete records with this SQL clause,

DELETE FROM TABLE WHERE ID = 2

I need to always leave one record if the table is count = 1, even if "ID = 2". How can i do this?

+2


source to share


3 answers


Add a WHERE clause to provide more than one line:



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


source


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

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


Perhaps add an if-statement to ensure that count is above 1.

+1


source


The easy 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 are doing this multiple delete that empties the table?

DELETE FROM TABLE WHERE ID BETWEEN 2 AND 5

so, randomly re-fill 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, this requirement is a bit dangerous and vague. In English, OK, "there is always at least one row in the table", but in practice "which row?"

+1


source






More articles:


All Articles