SQL UPDATE

If you work with databases, sooner or later you will have to update some of the existing information in one or more tables, and you'll have to use the SQL UPDATE clause. If you remember in the SQL INSERT chapter, we inserted a new row for BMW car in the Cars table, but we didn't have the color of this car at the time, so we left it empty (NULL value).

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Silver
Honda	Accord EX	2002	Black
Lexus	ES 350	2008	Silver
BMW	3 Series Coupe	2009	NULL

However today we learnt that the BWM is red, and we need to update our table accordingly. Here is how to do that:

UPDATE Cars SET Color = 'Red' WHERE CarMake = 'BMW' AND Model = '3 Series Coupe' AND Year = 2009;

The UPDATE statement is followed by the name of the table we are updating. The second line starts with the SET statement followed by the name of the column being updated and the new column value. The third line is the familiar WHERE clause specifying, which row exactly we are updating. The result of the update above will be:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Silver
Honda	Accord EX	2002	Black
Lexus	ES 350	2008	Silver
BMW	3 Series Coupe	2009	Red

You may ask a valid question here = "What if we need to have more than one BMW 3 Series Coupe, manufactured in 2009, which is red in color?". In this case it will be wise to introduce additional column in our table, which is unique - the car VIN number for example. This way we can differentiate between rows that have similar values.

You can update more than one column with a single SQL UPDATE. For example, you just learnt that the color of the BMW is Red and the year it was manufactured is not 2009, but 2008. To correct the data, you must run the following SQL statement:

UPDATE Cars SET Color = 'Red' AND Year = 2008 WHERE CarMake = 'BMW' AND Model = '3 Series Coupe' AND Year = 2009;

And here is what we get:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Silver
Honda	Accord EX	2002	Black
Lexus	ES 350	2008	Silver
BMW	3 Series Coupe	2008	Red

You can also update multiple rows with a single UPDATE. If you want to change all rows that have Silver in the Color column to Gray, you can do it as follows:

UPDATE Cars SET Color = 'Gray' WHERE Color = 'Silver';

The result of the SQL statement will be:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Gray
Honda	Accord EX	2002	Black
Lexus	ES 350	2008	Gray
BMW	3 Series Coupe	2008	Red