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:
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:
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 email@example.com.
What happens if you remove the WHERE clause and your SQL query looks like this:
The answer is that all Email entries in the Users table will be changed to firstname.lastname@example.org. 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:
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:
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.