Tables store data that can be used or referenced in the future. Over time, it is common to modify this data to ensure it remains relevant and up-to-date. In this article, you will learn three essential SQL commands to modify data: UPDATE, INSERT, and DELETE.
UPDATE: Changing Existing Data
The UPDATE statement allows you to modify existing records in a table.
Example:
Imagine you have a Product Information Table (product_info) for your kitchenware business. This table contains details about all products, including attributes such as product_id, product_name, weight, material, fabrication_country, cost, and price.
Before Update:
If the cost of borosilicate glass, used in your glass baking pan, increases, you need to update the cost data for this specific product.
SQL Script:
UPDATE product_info
SET cost = cost * 1.25
WHERE product_name = ‘baking pan’ AND material = ‘glass’;
After Update:
INSERT: Adding New Data
The INSERT statement allows you to add new records to a table.
Example:
As environmental concerns grow, sustainable materials are becoming popular. You decide to introduce a new baking pan made of carbon steel, which is durable, versatile, and heat-tolerant. You can use INSERT to add this new product to your table.
Before Insert:
SQL Script:
INSERT INTO product_info (product_id, product_name, weight, material, fabrication_country, cost, price)
VALUES (235, ‘baking pan’, 0.25, ‘carbon steel’, ‘France’, 9, 16);
After Insert:
DELETE: Removing Data
The DELETE statement allows you to remove records from a table.
Example:
Due to global supply chain disruptions, you decide to discontinue a stoneware baking pan. To remove it from your product catalog, use the DELETE statement.
Before Delete:
SQL Script:
DELETE FROM product_info
WHERE product_name = ‘baking pan’ AND material = ‘stoneware’;
After Delete:
Conclusion
Understanding how to modify data in SQL is crucial for maintaining accurate and up-to-date databases. Here’s a quick recap:
- UPDATE modifies existing records.
- INSERT adds new records.
- DELETE removes records from a table.
Mastering these commands will help you manage databases efficiently and keep your data relevant!
