sql, delete rows only when affected with a previous UPDATE command

Question

I only want to update 1 row (id) and only IF the status is == to stat

UPDATE link_list SET status=0 WHERE id=@id AND status=@stat;

Now i want to delete all rows but ONLY if the above was affected, how do i change the below to only delete if the above was affected?

DELETE link_list WHERE linkId=@id;

Answer 1

You can only delete rows where the status is 0 - you cannot directly say if that was in the last update

To delete where status is 0

DELETE link_list WHERE linkId=@id and statua = 0

Answer 2

What DB?

In datbases that support OUTPUT clause (SQL Server, ORacle, I think DB2) is easy:

UPDATE link_list SET status=0 
OUTPUT @linkID= DELETED.@id
WHERE id=@id AND status=@stat;

DELETE link_list WHERE linkId=@linkID;

Another option is to use a foriegn key constraint with ON DELETE CASCADE and let the engine do the delete for you.

Answer 3

I second the comment by @Rahuls suggesting using a trigger. Here's another alternative:

UPDATE link_list SET status=0, id=(@affectedid:=id) WHERE id=@id AND status=@stat;

DELETE link_list WHERE linkId = @affectedid;

The @affectedid user variable should be NULL if no rows were matched, so the DELETE will be a no-op. But if you are likely to do this more than once in a given session, the value of @affectedid could carry over from an earlier update, so it would be prudent to set it to NULL explicitly.

NB: I have not tested this, so be careful and test thoroughly.

Answer 4

You could try checking @@rowcount, which gives you the number of rows affected by the previous statement.