For an instructor lead, in-depth look at learning SQL click below.
Welcome to the world of SQL, an essential tool for any data work, including all the ins and outs of manipulating and extracting data. Today, we will be focusing on the SET clause – a tool that allows us to update records in a table with precision.
Understanding SQL Update and the SET Clause
In SQL, the UPDATE statement is used to modify the existing records in a table. It’s combined with the SET clause to specify the column you’d like to update and the new value you’d like to use. Here is the basic SQL UPDATE statement:
|
1 2 3 4 5 |
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; |
Updating a Single Record
Let’s look at an example where we will update a customer’s email in a hypothetical customers table:
|
1 2 3 4 5 |
UPDATE customers SET email = <a href="mailto:'newemail@domain.com'" >'newemail@domain.com'</a> WHERE customer_id = 1; |
In the example above, the customer with an id of 1 had their email updated to ‘newemail@domain.com’.
Updating Multiple Records
We can also update multiple records at once. For instance, if we wanted to update all customers from ‘New York’ to have a status of ‘preferred’:
|
1 2 3 4 5 |
UPDATE customers SET customer_status = 'preferred' WHERE city = 'New York'; |
Here, all customers from New York are now preferred customers.
Updating Multiple Columns
The SET clause can also be used to update multiple columns at the same time. Suppose we need to update both the email and the status of a specific customer. This can be achieved as follows:
|
1 2 3 4 5 |
UPDATE customers SET email = <a href="mailto:'newemail@domain.com'" >'newemail@domain.com'</a>, customer_status = 'preferred' WHERE customer_id = 1; |
This time, the customer with an id of 1 had both their email and customer status updated.
In Conclusion
The SET clause, in combination with the UPDATE statement, is a powerful tool in SQL that allows data analysts to modify existing data with precision. Whether you need to update a single record, multiple records, or multiple columns, the SET clause has got you covered!