Insert, update, delete

Everything in the chapters so far was a reading operation. Run it as often as you like and the data does not change; get it wrong and all that happens is that no result comes out.

This chapter is different. It deals with changing data. Adding a row, rewriting what is in one, deleting one. In app terms, that is posting, editing and deleting. Almost no app only reads, so it is at this point that you become able to build one.

There are three operations to learn.

• insert — add a new row to a table, saying which columns get what
• update — rewrite what is in a row that already exists
• delete — take a row away

The writing itself is not hard. What is hard is that there is no taking it back.

Get a condition wrong while reading and you only got a strange result. Get a condition wrong in an update or a delete and the data really is broken. Forget the condition altogether and either every row in the table becomes the same value, or every row goes. It happens in real workplaces, and no "undo" is provided.

So there is one habit to take on. Before you change anything, read with the same condition.

• first, with a reading statement, show the rows that match the condition
• look at what came out and satisfy yourself that it is exactly the rows you want to change
• then, with the condition unchanged, turn it into the statement that changes them

It is one extra step, and doing things in that order prevents nearly every accident. Professionals do it too.

When inserting, take care to keep the order of the columns and the order of the values together. You may leave out which columns you are filling, but then the day the table definition changes, your statement quietly breaks. It is a little longer to write the column names, and safer.

The common slip when updating is trying to rewrite every column. If only one column is changing, name only that column. The rest stay as they are. Try to write them all out and any column you forget ends up empty.

Remember, too, that you change things in bulk. If a hundred rows meet the condition, one statement rewrites all hundred. That is convenient, and it also means the damage from a mistake comes to a hundred rows.

This chapter also touches on the option of not deleting at all. You make it look deleted while really only raising a flag that says so. Then you can put it back, and the history survives. In a real app, actually removing a row is often the rarer thing.

When inserting, it is worth minding that you do not put the same data in twice. The submit button pressed twice, and two identical rows: a very common fault. Mark a column as not allowing repeats and the database itself will stop it.

Databases also have a mechanism for "run these together, and if one of them fails, undo the lot". It is there for work where succeeding by halves is unacceptable, such as taking money out of one account and putting it into another. Knowing the name alone is enough to look it up the day you need it.

At work it is also plain good manners to keep a copy of the data before you change it. When you are about to rewrite a great many rows, put the rows in question somewhere else first. Then a mistake can be undone. Break as much as you like in this course, but do remember it when the data is real.

Read, aggregate, join, change. With those in hand you have the whole of what SQL can do. What is left is to connect it to an app of your own and move some real data about.