UPDATE

Sometimes data need to be updated and this can be done with the UPDATE command. The syntax is fairly simple:

The UPDATEs will be applied in every row where the WHERE condition is met. Consider the Instruments table below

Let's say the Magnotron_2016 with instrument_id=8 finally gets a maintenance inspection date; this query will UPDATE it:

Without the WHERE condition, every single instrument would get that same last_maintenance_date of August 31st 2022. Because this is a risk, it is often a good idea to check first that the query will update the correct row, by constructing a SELECT query with the same WHERE clause. It is rarely correct to use UPDATE with no WHERE clause in a normalized relational database; note as well that if you UPDATE to a forbidden value (making a duplicate primary key, or breaking a foreign key relationship from either end, or a value with the wrong data type) the UPDATE will fail.

DELETE

When rows must be removed from a table altogether, DELETE is the command to use. Its syntax is even simpler than UPDATE:

All rows matching the WHERE condition will be deleted, although DELETE will fail if, for example, it would leave a foreign key orphaned. As with UPDATE, it is a good idea to test the WHERE clause (a quick substitution of DELETE to "SELECT *" will be instructive). With no WHERE clause, the table will be emptied of all rows. For an example, perhaps the instrument with instrument_id=8 was never maintained because it never existed; in that case it should be DELETEd (if it had taken measurements, of course, the foreign key relationship with Measurements would cause the DELETE to fail):