Detailed explanation of Update statement case in Sql

SQL (Structured Query Language) is a powerful language for managing and operating relational databases.UPDATEStatements are a method in SQL to modify records already present in databases. Here are some classicsUPDATEStatement case:

1. Update column values for specific rows:

UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 123;

This statement willemployeesIn the tableemployee_idFor employees of 123salaryIncrease by 10%.

2. Update multiple lines based on conditions:

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 5;

This statement willemployeesIn the tabledepartment_idFor all employees of 5salaryIncreased by 5%.

3.Use subquery to update:

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = 5)
WHERE department_id = 5;

This statement willdepartment_idFor all employees of 5salaryUpdated to the average salary of the same department.

4. Update multiple columns:

UPDATE employees
SET first_name = 'John', last_name = 'Doe'
WHERE employee_id = 123;

This statement willemployeesIn the tableemployee_idFor employees of 123first_nameandlast_nameUpdated to 'John' and 'Doe'.

5. Use JOIN to update the relevant table:

UPDATE orders
SET  = 'Shipped'
FROM orders
JOIN order_details ON orders.order_id = order_details.order_id
WHERE order_details.product_id = 456;

This statement willorder_detailsIn the tableproduct_idFor all orders of 456statusUpdated to 'Shipped'.

6. Use LIMIT to limit the number of update rows (supported in some database systems):

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 5
LIMIT 10;

This statement willemployeesIn the tabledepartment_idFor the top 10 employees of 5salaryIncreased by 5%.

7. Use CASE statement to perform conditional update:

UPDATE employees
SET salary = CASE
    WHEN department_id = 1 THEN salary * 1.1
    WHEN department_id = 2 THEN salary * 1.05
    ELSE salary
END
WHERE department_id IN (1, 2);

This statement is based ondepartment_idThe difference is, rightemployeesEmployees in the tablesalaryMake different proportions of increase.

In useUPDATEBe careful when performing statements, because once executed, the data in the database will be modified directly. It is best to use it before performing an update operationSELECTThe statement checks the data to be updated to ensure that the update conditions are correct. In addition, for important data update operations, it is recommended to perform them in the test environment first, and then perform them in the production environment after confirming that they are correct.

This is all about this article about the Update statement in Sql. For more related SQL update statement content, please search for my previous article or continue browsing the related articles below. I hope everyone will support me in the future!