Explain UPDATE in SQL

The UPDATE statement in SQL is used to modify the existing records in a table. Here is a general syntax for the UPDATE statement:

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

Key Points:

• table_name: The name of the table you want to update.
• SET column1 = value1, column2 = value2, ...: The columns and their new values.
• WHERE condition: Specifies which records to update. Without a WHERE clause, all records in the table will be updated.

Example 1: Update a Single Column

Consider a table named Employees:

To update the salary of the employee with EmployeeID 2:

UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 2;

Output:

Example 2: Update Multiple Columns

To update both the FirstName and Salary of the employee with EmployeeID 3:

UPDATE Employees
SET FirstName = 'Lawrence', Salary = 75000
WHERE EmployeeID = 3;

Output:

Example 3: Update All Records

To increase the salary of all employees by 10%:

UPDATE Employees
SET Salary = Salary * 1.10;

Output:

Example 4: Update Using a Subquery

Suppose we have another table Departments:

And we want to update the Salary in Employees based on the department they belong to (assuming there is a DepartmentID column in Employees):

UPDATE Employees
SET Salary = (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = 2)
WHERE DepartmentID = 1;

This query sets the salary of all employees in the HR department (DepartmentID = 1) to the average salary of the IT department (DepartmentID = 2).

Example 5: Update with JOIN

To update a table with a join condition, you can use the following structure:

UPDATE e
SET e.Salary = e.Salary + 1000
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'IT';

This query increases the salary of employees in the IT department by 1000.

Summary

• The UPDATE statement modifies existing records in a table.
• Use the WHERE clause to specify which records to update.
• Without a WHERE clause, all records in the table will be updated.
• You can update single or multiple columns, use subqueries, or join with other tables in your UPDATE statement.