Learn SQL for Developers/

...

/

Updating and Deleting Data

Updating and Deleting Data

Learn to update data in the tables and delete rows from the tables.

We'll cover the following...

Imagine we are managing our OnlineStore database and need to update product prices or remove discontinued items or customers update their addresses. To keep the database accurate, we need tools to modify or remove data effectively. Keeping our data current is essential for accurate reporting and customer satisfaction.

Let's explore how to update and delete data in SQL using the UPDATE and DELETE statements. We'll go step by step to:

  • Understand when and why to use the UPDATE statement.

  • Learn how to modify existing records effectively.

  • Understand when and why to use the DELETE statement.

  • Learn how to safely remove records from a table.

Updating data with UPDATE

Databases are built to adapt to change as data evolves over time. In an online store's database, the change can be correcting errors, updating prices, or editing stock levels. This is where the UPDATE statement becomes important. It allows us to modify one or more fields of an existing record without having to delete and re-insert the entire row.

To use UPDATE, we specify which table we want to change, then set the new value for one or more columns using the SET keyword, and state which record(s) should be changed using the WHERE clause. The syntax for using UPDATE is shown below:

UPDATE TableName
SET Column1 = Value1, Column2 = Value2, ...
WHERE condition;

For example, we need to increase the price of the Smartphone in the Products table by a small amount due to rising costs. If the current price is $635.79 and we want to make it $850.00 we can use the following:

MySQL
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
-- Retrieve the price value before the update
SELECT ProductName, Price
FROM Products
WHERE ProductName = 'Smartphone';
-- Update the price value
UPDATE Products     
SET Price = 850.00   
WHERE ProductName = 'Smartphone'; 
-- Retrieve the price value after the update
SELECT ProductName, Price
FROM Products
WHERE ProductName = 'Smartphone';
...