SQL Update Statement
In this lesson, you will learn how to modify or update the existing data in the table using SQL.
Update Statement In SQL
The UPDATE statement is used to update existing data in a table as follows.
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
The WHERE clause in the UPDATE statement specifies which record(s) should be updated. If you omit the WHERE clause, all the records will be updated!
Preparing A Database
Since you will learn how to modify existing data in a table, you must have a database table with some records to practice updating them. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(7,2)
);
-- Insert 5 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "Johnson", 800);
INSERT INTO employees VALUES (null, "Benjamin", "Anderson", 750);
INSERT INTO employees VALUES (null, "Olivia", "Smith", 980);
INSERT INTO employees VALUES (null, "William", "Davis", 845);
INSERT INTO employees VALUES (null, "Sophia", "Brown", 700);
Update Table Data Examples
The following query updates the salary value for the employee who has an id equal to 1.
Example 1
UPDATE employees
set salary = 1200
WHERE id = 1;
Output
The output means that 1 record in the table is updated successfully.
If you view the table now, you will notice that the salary value for the first record is changed to 1200 as follows.
| ID | first_name | last_name | salary |
|---|---|---|---|
| 1 | Emily | Johnson | 1200.00 |
| 2 | Benjamin | Anderson | 750.00 |
| 3 | Olivia | Smith | 980.00 |
| 4 | William | Davis | 845.00 |
| 5 | Sophia | Brown | 700.00 |
The following query updates the first_name and last_name values for the employee who has an id equal to 3.
Example 2
UPDATE employees
set first_name = 'Rachel', last_name = 'Dalton'
WHERE id = 3;
Output
The output means that 1 record in the table is updated successfully.
If you view the table now, you will notice that the first_name and last_name values for the third record are changed to "Rachel Dalton" as follows.
| ID | first_name | last_name | salary |
|---|---|---|---|
| 1 | Emily | Johnson | 1200.00 |
| 2 | Benjamin | Anderson | 750.00 |
| 3 | Rachel | Dalton | 980.00 |
| 4 | William | Davis | 845.00 |
| 5 | Sophia | Brown | 700.00 |
Disable Safe Mode In MySQL Workbench
Usually, updating multiple or all records at once is disabled by database servers and it's disabled by default in MySQL.
Now, if you try to update multiple or all records in MySQL you may get this error message:
To disable safe mode in MySQL Workbench navigate to Edit > Click on Preferences > Click on SQL Editor tab, check the Safe Updates option, Click Ok and reconnect.
Be sure that the Safe Update option is disabled to be able to execute the next queries.
Update Multiple Records Example
The following query updates the salary value for all employees if it's less than 900.
Example
UPDATE employees
set salary = 900
WHERE salary < 900;
Output
The output means that 3 records in the table are updated successfully.
If you view the table now, you will notice that the salary value is changed to 900 for three records as follows.
| ID | first_name | last_name | salary |
|---|---|---|---|
| 1 | Emily | Johnson | 1200.00 |
| 2 | Benjamin | Anderson | 900.00 |
| 3 | Rachel | Dalton | 980.00 |
| 4 | William | Davis | 900.00 |
| 5 | Sophia | Brown | 900.00 |
Update All Records Example
The following query updates the salary for all employees.
Example
UPDATE employees
set salary = 800;
Output
The output means that all records in the table are updated successfully.
If you view the table now, you will notice that the salary value for all records is changed to 800 as follows.
| ID | first_name | last_name | salary |
|---|---|---|---|
| 1 | Emily | Johnson | 800.00 |
| 2 | Benjamin | Anderson | 800.00 |
| 3 | Rachel | Dalton | 800.00 |
| 4 | William | Davis | 800.00 |
| 5 | Sophia | Brown | 800.00 |
Update Records Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "products" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTtitle VARCHAR(100)description VARCHAR(500)price DECIMAL(7,2)
3- Write a query that inserts 5 rows in the "products" table.
4- Update the price for the second product.
5- Write a query that selects all records from the "products" table ordered by their prices.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS products (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
description VARCHAR(500),
price DECIMAL(7,2)
);
INSERT INTO products (title, description, price) VALUES ('Dell XPS 13 Laptop', 'The Dell XPS 13 is a powerful and sleek ultrabook, featuring a 13.3-inch InfinityEdge display, Intel Core i7 processor, 16GB RAM, and 512GB SSD. With a lightweight design and long battery life, it\'s perfect for productivity on the go.', 1299.99);
INSERT INTO products (title, description, price) VALUES ('Apple iPhone 13 Pro Max', 'The iPhone 13 Pro Max is Apple\'s flagship smartphone, boasting a stunning 6.7-inch Super Retina XDR display, A15 Bionic chip, triple-lens camera system, and 128GB of storage. Experience exceptional performance and photography with this premium device.', 1099.00);
INSERT INTO products (title, description, price) VALUES ('Fitbit Charge 5 Fitness Tracker', 'The Fitbit Charge 5 is a feature-packed fitness tracker that monitors your heart rate, sleep, and activity. It includes built-in GPS, a vibrant AMOLED display, and supports smartphone notifications. Stay motivated and track your health and fitness goals with ease.', 149.99);
INSERT INTO products (title, description, price) VALUES ('Breville Barista Express Espresso Machine', 'The Breville Barista Express is a top-of-the-line espresso machine with a built-in conical burr grinder. Create barista-quality coffee at home with customizable settings for grind size, brew temperature, and milk frothing. Elevate your morning coffee routine with this premium appliance.', 699.00);
INSERT INTO products (title, description, price) VALUES ('Amazon Echo Show 10 (3rd Gen)', 'The Amazon Echo Show 10 is a smart speaker with a 10.1-inch HD screen that can rotate to follow you around the room during video calls or while streaming content. It features Alexa voice assistant, smart home control, and premium audio for a complete smart home experience.', 249.99);
UPDATE products
SET price = 899
WHERE id = 2;
SELECT * FROM products
order by price;
