UPDATE Statements

Estimated reading: 2 minutes 21 views

1. Write a query to update a specific column in a table

To update a specific column, use the UPDATE statement followed by the table name, SET clause to specify the column to be updated, and the new value.
Example:

				
					UPDATE employees 
SET salary = 55000 
WHERE id = 1;

2. How do you update multiple columns in a single query?

You can update multiple columns by separating each column-value pair with a comma in the SET clause.
Example:

				
					UPDATE employees 
SET salary = 60000, role = 'Senior Manager' 
WHERE id = 1;

3. Why is the WHERE clause critical in an UPDATE statement? What happens if it is omitted?

The WHERE clause is critical because it specifies which rows should be updated. Without it, all rows in the table will be updated, leading to unintended changes.
Example:
Without WHERE, all rows would be updated:

				
					UPDATE employees 
SET salary = 50000;

4. Write a query to update rows in one table based on conditions in another table

You can update rows in one table by using a JOIN with another table in the UPDATE statement.
Example:

				
					UPDATE e 
SET e.salary = d.new_salary 
FROM employees e 
JOIN departments d 
ON e.department_id = d.id 
WHERE d.department_name = 'Sales';

5. How can you safely update rows in a table to prevent accidental overwrites?

You can prevent accidental overwrites by using a WHERE clause with a condition that limits the rows affected. Additionally, you can perform a backup or use a transaction to ensure data integrity.
Example:

				
					-- Safe update with a specific condition
UPDATE employees 
SET salary = 60000 
WHERE id = 5 AND salary < 60000;