1. Update your data
In the last lesson you've learned how to create & populate tables in your database. Now you will learn how to update the information in these tables.
2. UPDATE syntax
The syntax for the UPDATE statement is used like so. You first need to specify the table name that you would like to update and then list the columns and the new values you'd like to update after the SET command.
Let's work through a couple of examples.
3. UPDATE a column
Let's say that you learned that the emails of your clients must all be lower case and would like to fix this in the customer table.
Using the UPDATE statement you can simply SET the email column to be lowercase by using the LOWER function.
4. UPDATE & WHERE
Let's expand on this scenario, except that you know that only customers who are active need to have their emails updated to lowercase.
To do this you simply add a WHERE clause like so. This ensures that only the records that meet this criteria are modified.
5. UPDATE using subqueries
You can make this logic as complex as you need it to be. For instance here we've learned this update needs to only be applied for users from the city of Woodridge.
This means that another table needs to be brought in via a subquery. Here you need your subquery to return the address ids from the address table where the city is equal to Woodridge.
Once the subquery returns the address ids of the residents they can be used as a part of a WHERE statement as shown in this example.
6. Be careful when modifying tables
As the famous quote goes: "with great power comes great responsibility". This is especially the case when working with company databases. Making modifications to tables can impact both users and downstream processes that rely on these tables.
This is why you should consider the following precautions before modifying a table:
First, you need to ensure that you have the right permissions to modify a table. Oftentimes, this is a very valuable control put in place by the database administrators.
Next, you should make sure that you understand how this table is used and whether the changes you make to it can have an impact on downstream processes.
Finally, before you execute your UPDATE query you should consider testing that the WHERE conditions are correctly defined by using them in a SELECT command and reviewing what data is returned.
7. Let's UPDATE
Now, let's try these tools out.