Thursday, January 31, 2008

SQL proposal: Select deleted and updated records

Here's another proposal for an addition/extension to SQL. In the earlier post I said that it should be possible to get feedback from delete- and update-statements. Here's an even better proposal that adds some syntax to SQL and allows you to fetch the records that have been updated or deleted.
Here's how it should look like:

delete from customers where marked_as_deleted="true" select id, name

Executing this will behave like a normal select-statement, while the results will be the id and the name of the customers that you have just deleted. With this it is possible to create a message like
The customers "Tom", "Bill" and "Lara" have been successfully deleted.

This is a good idea because it gives the user more feedback on the operation that just has been completed. With update-statements it's very similar:
update customers set marked_as_deleted="true" where last_action > "01.01.1998" select id, name

A possible user-feedback would be
The customers "Tom", "Bill" and "Lara" have just been marked for deletion, because they are too old.

You can simulate this, of course, by first selecting the customers and then running an update/delete with a) the same where-clause (which is unsafe because the data might've been changed in the meantime) or b) for each fetched id, which is slower because it doesn't let the server handle the whole operation on it's own.

No comments:

Post a Comment