SQL UPDATE Statement
The UPDATE statement is used to modify existing records in a table.
Basic Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;Example 1: Updating a Single Column
UPDATE employees
SET salary = 65000
WHERE employee_id = 101;This query updates the salary of the employee with employee_id 101 to 65000.
Example 2: Updating Multiple Columns
UPDATE employees
SET salary = 70000, department = 'Marketing'
WHERE employee_id = 102;This query updates the salary and department of the employee with employee_id 102.
Example 3: Updating Multiple Records
UPDATE employees
SET department = 'Sales'
WHERE department IS NULL;This query updates the department to ‘Sales’ for all employees where the department is currently NULL.
Example 4: Using Subqueries in UPDATE
UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE department = 'HR';This query sets the salary of employees in the ‘HR’ department to the average salary of all employees.
Example 5: Updating with JOIN
UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.salary = e.salary * 1.1
WHERE d.department_name = 'Sales';This query gives a 10% raise to all employees in the ‘Sales’ department by joining the employees and departments tables.
The UPDATE statement is a powerful tool for modifying existing data in your database. Always use the WHERE clause to specify which records should be updated. Omitting the WHERE clause will result in all records in the table being updated, which might not be the intended effect.