An SQL Update statement
An SQL UPDATE statement is used to modify the existing records or rows in a database table. Based on a given condition, it permits you to change the values of one or more columns in one or more rows.
When you update the existing records in a table, please be careful, but you will need to use the where clause otherwise, all records will get updated without a where clause.
Here is the syntax of SQL UPDATE statement:
UPDATE table_name
SET column1 = value1, column2 = value2, column3 = value3,
WHERE condition;
Let’s break down this syntax with some examples:
SQL Update with examples
Example 1: Updating a Single Record
Suppose you have a table called employees with columns employee_id, first_name, and last_name, and you want to change the last name of the employee with employee_id equal to 151 to Smith.
UPDATE employees
SET last_name = ‘Smith’
WHERE employee_id = 151;
This SQL statement will update the last_name column to Smith for the employee with employee_id 151.
Example 2: Updating Multiple Records
Let’s say you want to give all employees in Marketing department a salary increase of 10%. The employees table also has a department and salary
column.
UPDATE employees
SET salary = salary * 1.10
WHERE department = ‘Marketing’;
This SQL statement updates the salary column for all employees in the Marketing department by multiplying their current salary by 1.10, effectively giving them a 10% raise.
Example 3: Updating Multiple Columns
Multiple columns can be updated with a single SQL UPDATE command. Suppose you have a table called products with columns price, discount, and final_price. You want to increase the price by 5% and update the discount to 15%.
UPDATE products
SET price = price * 1.05,
discount = 15;
This SQL statement updates both the price and discount columns in the products table.
Example 4: Updating All Rows
If you omit the WHERE clause, the SQL UPDATE statement will affect all rows in the table. Use this with caution, as it can lead to unintended changes.
UPDATE employees
SET salary = salary + 1000;
This SQL update statement would increase the salary of all employees by 1000 units.
Consider utilizing a WHERE clause to target the precise rows you wish to edit when using UPDATE statements. If you don’t, there could be unintended data changes throughout the entire table, which would compromise the integrity of the data.
NOTE: Before running a UPDATE statement, make sure it is correct.