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:

CopyUPDATE 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:

CopyUPDATE 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:

CopyUPDATE 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:

CopyUPDATE 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:

CopyUPDATE 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 an UPDATE statement. For example:

CopyUPDATE 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 the UPDATE statement. A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. For example:

CopyWITH 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:

CopyUPDATE 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:

CopyUPDATE 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.