SQL Update Statement

1. SQL Update Statement is used to modify existing records in a table. It allows you to update one or more columns in a table at the same time.
2. The SQL Update syntax includes the table name, the columns to update, the new values to assign, and an optional WHERE clause to specify the rows to update.
3. When updating a single column, you can use the SQL Update Statement in conjunction with the SET and WHERE clauses. The WHERE clause is optional but helps to update specific rows. Without a WHERE clause, all rows in the table will be updated.
4. The SQL Update Statement can update multiple columns in a single query. You can specify multiple column-value pairs within the SET clause to modify multiple columns simultaneously.
5. The SQL Update Statement can update multiple rows in a table. By using a WHERE clause that covers more than one row, you can update specific rows based on a condition. If WHERE clause is omitted, all rows in the table will be updated, which can be risky if unintended.

The SQL Update Statement is a SQL command that is used to modify existing records in a table. We can use this statement in conjunction with a WHERE clause to update selected rows, or without a WHERE clause to update all rows in the table. The SQL Update Statement can modify one or more columns at the same time.

The general syntax of the Update Statement Sql is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE condition;

In this Update Statement Sql syntax:

1. table_name: is the name of table you want to update.
2. SET: is the keyword that tells SQL the changes you want to make.
3. column1, column2, ..., columnN are the columns you wish to change.
4. value1, value2, ..., valueN are the new values you want to place into the columns.
5. SET column1 = value1, column2 = value2, ...: This indicates the specific column(s) you want to update and the new value(s) you want to assign.
6. WHERE: is the keyword that allows you to specify which rows you want to update. The condition must be met for a row to be updated. If the WHERE clause of the update query in SQL is omitted, all rows/records in the table will be updated, which can be dangerous if not intended.

To update a single column in a SQL table, use the SQL Update Statement in conjunction with the SET and WHERE clauses. The WHERE clause is optional, but without it, all rows will be updated. Let's understand this with an example.

Consider a table named "Employees", which contains the following data:

Now if we want to Update SQL table, we will Update SQL data to increase John's salary from 5000 to 5500. The SQL Update Statement for this operation would be:

UPDATE Employees
SET Salary = 5500
WHERE Name = 'John';

Executing this Update SQL Query will modify John's salary in the "Employees" table. But remember, in this case, you should only use Name = ‘John’ if you know there is only one name “John” in the table, otherwise it will update salary for all John’s. The recommended method is to use a unique “ID” in the WHERE clause of SQL Update Statement. Like this:

UPDATE Employees
SET Salary = 5500
WHERE ID = 1;

After running this Update SQL Statement, the table will reflect the change in John's salary. The SQL table update is highlighted in orange:

The SQL Update Statement can also be used to update multiple columns in a single query. Consider the following scenario: Anna has been promoted from Manager to Director and her salary has increased from 6000 to 7000. We want to Update SQL Table "Employees" to reflect these changes. Here's the appropriate Update SQL Statement:

UPDATE Employees
SET Position = 'Director', Salary = 7000
WHERE Name = 'Anna';

After executing this SQL Update Query, the "Employees" table will reflect Anna's new position and salary. We are using the “Name” column in WHERE Clause just for examples, but better you should use a unique ID in WHERE Clause, as there might be multiple Employees with the name “Anna”.

The updated table:

There might be cases such that when you need to update multiple rows in a table. In such scenarios, the SQL Update Statement is used with a WHERE clause that covers more than one row. For instance, suppose we want to give all Engineers a salary increase of 500. We can accomplish this with the following Update SQL Statement:

UPDATE Employees
SET Salary = Salary + 500
WHERE Position = 'Engineer'

After running this SQL Update Query, all engineers in the "Employees" table will have their salaries increased by 500.

Sometimes you might need to update all rows in a table. In this case, the SQL Update Statement is used without a WHERE clause. Let's say the company decides to give a bonus of 200 to all employees. Here's the Update SQL Query for this operation:

UPDATE Employees
SET Salary = Salary + 200;

After executing this SQL Update Query, the "Employees" table will reflect the increased salary for all employees. Look at the following SQL table update!

The SQL Update Statement can also be used in conjunction with JOIN to update records in a table based on a condition in another table. Let's take an example of two tables: "Employees" and "Departments".

The "Departments" table looks like this:

And the "Employees" table is updated to include a DepartmentID column:

Now, let's say we want to give employees a bonus based on their department. We can use the SQL Update Statement with JOIN to accomplish this. Here's the Update SQL Statement for this operation:

UPDATE Employees 
SET Salary = Salary + Departments.Bonus
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

After executing this Update SQL Statement, the "Employees" table will reflect the new salaries after adding the respective department bonuses.

The SQL Update statement can also be used to update rows based on the results of a subquery. Let's consider a possible scenario where we want to update salary of employees based on their performance rating. We have a separate table called Performance, which contains the columns EmployeeID and Rating.

“Employees” table:

“Performance” table:

We can update the salary of employees who have a rating above a certain threshold as follows:

UPDATE Employees
SET Salary = Salary * 1.05
WHERE EmployeeID IN (
  SELECT EmployeeID
  FROM Performance
  WHERE Rating > 4
);

In this example, the SQL Update statement updates the Salary of employees whose EmployeeID appears in the result set of the subquery. The subquery selects all EmployeeID values from the Performance table where the Rating is greater than 4. The selected employees receive a 5% salary increase.