update in SQL
The UPDATE
statement is used to modify the values of existing rows in a table in a database.
Here is the basic syntax of an UPDATE
statement:
UPDATE table SET column1 = value1, column2 = value2, ... WHERE condition;
Here is an example of an UPDATE
statement that updates the salary
column for all rows in the employees
table:
UPDATE employees SET salary = salary * 1.1;
This UPDATE
statement will increase the value of the salary
column for all rows in the employees
table by 10%.
You can specify a WHERE
clause to filter the rows that are updated. For example:
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';
This UPDATE
statement will increase the value of the salary
column for all rows in the employees
table where the department
column is "Sales" by 10%.
points to consider when working with the Update
- You can use the
RETURNING
clause to return the values of the updated rows. For example:
UPDATE employees SET salary = salary * 1.1 RETURNING *;
This UPDATE
statement will increase the value of the salary
column for all rows in the employees
table by 10%, and return all columns of the updated rows.
- You can use the
FROM
clause to specify additional tables or views to join to the target table. For example:
UPDATE employees e SET salary = s.new_salary FROM salary_updates s WHERE e.employee_id = s.employee_id;
This UPDATE
statement will update the salary
column for all rows in the employees
table by using the new_salary
column from the salary_updates
table. The FROM
clause allows you to join the employees
table to the salary_updates
table and use columns from both tables in the SET
and WHERE
clauses.
- You can use the
CASE
expression to perform conditional logic in anUPDATE
statement. For example:
UPDATE employees SET salary = (CASE WHEN department = 'Sales' THEN salary * 1.2 ELSE salary * 1.1 END);
This UPDATE
statement will increase the value of the salary
column for all rows in the employees
table by 20% if the department
column is "Sales", or by 10% if the department
column is anything else.
- You can use the
FROM
clause to specify a common table expression (CTE) that can be used in theUPDATE
statement. A CTE is a temporary named result set that you can reference within aSELECT
,INSERT
,UPDATE
,DELETE
, orCREATE VIEW
statement. For example:
WITH salary_updates AS ( SELECT employee_id, salary * 1.1 AS new_salary FROM employees WHERE department = 'Sales' ) UPDATE employees e SET salary = s.new_salary FROM salary_updates s WHERE e.employee_id = s.employee_id;
This UPDATE
statement will use the salary_updates
CTE to calculate the new salary values for all rows in the employees
table where the department
column is "Sales". The FROM
clause allows you to join the employees
table to the salary_updates
CTE and use columns from both tables in the SET
and WHERE
clauses.
- You can use the
RETURNING
clause to return the values of the updated rows. For example:
UPDATE employees SET salary = salary * 1.1 RETURNING *;
This UPDATE
statement will increase the value of the salary
column for all rows in the employees
table by 10%, and return all columns of the updated rows.
- You can use the
ON CONFLICT
clause to handle situations where a unique constraint is violated when updating a row. For example:
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 1 ON CONFLICT (employee_id) DO NOTHING;
This UPDATE
statement will update the salary
column for the row with employee_id
1 in the employees
table by 10%. If the employee_id
column already exists in the table and violates a unique constraint, the ON CONFLICT
clause will do nothing.