SQL UPDATE

So far we only looked at retrieving data from SQL database, but we never talked about modifying/updating data. The SQL UPDATE command is used to modify data stored in database tables.

If you want to update the email of one of the users in our Users table, you'll use a SQL statement like the one below:

UPDATE Users SET Email = 'new_email_goes_here@yahoo.com' WHERE Email = 'sgrant@sgrantemail.com'

Let's examine the statement above. The first row has the keyword UPDATE followed by the name of the table we are updating. The second row is the row that defines the changes made to the database fields using the keyword SET followed by the column name, equal sign and the new value for this column. You can have more than one assignment of new value after the SET keyword, for example if you want to update both the email and the city you will use the SQL statement below:

UPDATE Users SET Email = 'new_email_goes_here@yahoo.com', City = 'San Francisco' WHERE Email = 'sgrant@sgrantemail.com'

The third line is our WHERE clause, which specifies which record(s) to update. In our case it says to update the Email filed of the row having email sgrant@sgrantemail.com.

What happens if you remove the WHERE clause and your SQL query looks like this:

UPDATE Users SET Email = 'new_email_goes_here@yahoo.com'

The answer is that all Email entries in the Users table will be changed to new_email_goes_here@yahoo.com. Most likely you will not want to do something like this, but you might have a case when you need to update several table rows at once. For example if one of the company's offices has been moved from San Francisco to Los Angeles you might want to update all users with City San Francisco to Los Angeles (we assume that the employees have moved too). To do that, use the following SQL statement:

UPDATE Users SET City = 'Los Angeles' WHERE City = 'San Francisco'

In both UPDATE example having WHERE clause above, I've changed a table field to new value, using the same field in the WHERE clause criteria. This was purely coincidental and you can update different field(s) than the one used in your WHERE criteria, for example:

UPDATE Users SET Email = 'new_email_goes_here@yahoo.com' WHERE FirstName = 'Stephen' AND LastName = 'Grant'

When updating make sure that the WHERE clause criteria you have specified updates only the rows you want. Using the example above if you didn't have FirstName = 'Stephen' in your WHERE criteria, you would have updated 2 records (Susan Grant and Stephen Grant), because both these users have one and the same last name.