Recipe Title - SQL UPDATE: How to Use UPDATE Statements in SQL? 

If you're diving into the world of SQL, mastering the UPDATE statement is essential for maintaining the accuracy and integrity of your database. The SQL UPDATE statement allows you to modify existing records in a table, ensuring that your data stays current and relevant. Check out this recipe to explore the intricacies of the SQL UPDATE statement, covering topics such as basic syntax, updating multiple columns, and using JOIN clauses for more complex updates.

Basic Syntax of SQL UPDATE Statement 

The fundamental structure of an SQL UPDATE statement is as follows:

Syntax:
UPDATE table_name

SET column1 = value1, column2 = value2, …

WHERE condition;

You must be very careful while using the UPDATE statement because if you don’t specify the WHERE clause you will end up updating all the rows of the mentioned column.

SQL Project for Data Analysis using Oracle Database-Part 4 

Examples of Basic SQL UPDATE Statements

SQL UPDATE Column: Let us add an income column to the customers table first.

Code: 

-- adding new column income

ALTER TABLE customers ADD income INTEGER;

-- updating the column values for pre-existing entries
UPDATE customers set income = 90000000 where customer_id=101;
UPDATE customers set income = 10000000 where customer_id=102;
UPDATE customers set income = 78000000 where customer_id=103;
UPDATE customers set income = 56000000 where customer_id=104;
UPDATE customers set income = 78000000 where customer_id=105;

-- viewing the data
SELECT * FROM customers;

Output:

+-------------+----------------+------+------------+---------+----------+

| customer_id | customer_name  | age  | city       | country | income   |

+-------------+----------------+------+------------+---------+----------+

|         101 | Thomas Shelby  |   30 | Birmingham | England | 90000000 |

|         102 | Grace Burgess  |   28 | Dublin     | Ireland | 10000000 |

|         103 | Alfie Solomons |   40 | London     | England | 78000000 |

|         104 | Michael Gray   |   22 | New York   | USA     | 56000000 |

|         105 | May Carleton   |   29 | Sheffield  | England | 78000000 |

+-------------+----------------+------+------------+---------+----------+

5 rows in set (0.00 sec)

SQL Update Multiple Columns: You can also update multiple columns as follows:

Code:

-- updating city column

UPDATE customers SET city = "Manchester" WHERE country = "England";

-- displaying the changes
SELECT * FROM customers;

Output:

+-------------+----------------+------+------------+---------+----------+

| customer_id | customer_name  | age  | city       | country | income   |

+-------------+----------------+------+------------+---------+----------+

|         101 | Thomas Shelby  |   30 | Manchester | England | 90000000 |

|         102 | Grace Burgess  |   28 | Dublin     | Ireland | 10000000 |

|         103 | Alfie Solomons |   40 | Manchester | England | 78000000 |

|         104 | Michael Gray   |   22 | New York   | USA     | 56000000 |

|         105 | May Carleton   |   29 | Manchester | England | 78000000 |

+-------------+----------------+------+------------+---------+----------+

5 rows in set (0.00 sec)

Taking it a Step Further: SQL UPDATE with JOIN

In scenarios where you need to update records in one table based on values from another, the SQL UPDATE with JOIN comes into play. This is particularly useful for complex updates involving multiple tables.

Example of SQL Server UPDATE with JOIN

UPDATE orders

SET orders.status = 'Shipped'

FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.country = 'USA';

Here, the orders table is updated to set the status to 'Shipped' for all orders placed by customers in the USA.

SQL UPDATE from SELECT: Efficient Updates with Subqueries

Another powerful feature is updating data based on the results of a SELECT statement. This is achieved using the SQL UPDATE from SELECT syntax.

Example of SQL UPDATE from SELECT

UPDATE products

SET stock_quantity = stock_quantity - 10

FROM products

WHERE category = 'Electronics';

Here, the stock quantity of products in the 'Electronics' category is decreased by 10.

GCP Data Ingestion with SQL using Google Cloud Dataflow

SQL Server UPDATE: Tailoring to Your Needs

SQL Server provides additional functionalities for the UPDATE statement. For instance, you can use the OUTPUT clause to capture the results of the update operation.

Example of SQL Server UPDATE with OUTPUT

UPDATE employees

SET salary = salary * 1.1

OUTPUT INSERTED.employee_id, INSERTED.salary

WHERE department_id = 102;

This not only updates the salary but also outputs the employee ID and the updated salary.

Learn more about SQL UPDATE Statements with ProjectPro! 

Mastering the SQL UPDATE statement is crucial for maintaining a well-functioning and up-to-date database. Whether you're making simple changes to a single column or executing complex updates across multiple tables, understanding the nuances of SQL UPDATE will empower you to manage your data effectively. Start experimenting with the provided real world practical projects to enhance your SQL skills and unlock the full potential of database management. Check out ProjectPro to gain access to 270+ projects on data analysis, data science and big data.