Delete from

Better testing the delete command on a safe environment, given that we are about to remove data. So I switch back to use my temp schema.

Actually, if you delete by mistake rows in a table you can restore the previous status through the rollback command. The deletion becomes irreversible only when you confirm it using the commit command.

In any case, as they say, better safe than sorry, so at least the first try on delete is better if given on a useless table.

Nothing is more useless here around than the doughnut table that I created in my test schema. Currently it has a few rows in it, some of them with the CREATED field set to NULL. I decided to remove them, so I execute this SQL command:

delete from doughnut where created is null;

I can now check the result of my job, for instance selecting all the rows left in the table:

select * from doughnut;

And if I'm happy of the result I commit the operation. Otherwise I can easily change my mind:

rollback;

Bad idea is calling delete without the where clause, 'cause that means "delete all rows in the table". And since the database should generate a way to rollback the operation, that could result in a very long time execution, if we are working on a big table. To perform such an operation is better firstly being really sure of what we are about to do, and secondly using the truncate command, that remove all the table's rows in a whiff (but no rollback available!).

Post written while having fun reading Head First SQL