Working with Data
Inserting, Updating, and Deleting
You have learned how Subqueries works in the previous article. Now it’s time to explore how to insert, modify and delete data in your tables. This is where SQL becomes a powerful tool for maintaining and managing your database.
Let’s walk through how to add, change, and remove data with clear examples using SQL Server.
Inserting Data into Tables
Adding new data to a table is done using the INSERT
statement. Think of it as adding a new row to your spreadsheet.
Syntax:
Example: Adding a New Product
Imagine you’ve just launched a new product, “Fitness Band,” and want to add it to the Products table.
Result: A new row is added to the table:
product_id | product_name | category | price | stock |
---|---|---|---|---|
6 | Fitness Band | Fitness | 3500 | 20 |
This is how simple it is to add new data!
Updating Data in Tables
What if your inventory changes, and you need to update the quantity of products in stock? That’s where the UPDATE
statement comes in.
Syntax:
Example: Restocking a Product
Suppose a new shipment of Yoga Mats arrives, and you need to increase their stock by 30 units.
Result: The stock for “Yoga Mat” is updated:
product_name | stock |
---|---|
Yoga Mat | 80 |
Deleting Data from Tables
Sometimes, you’ll need to remove outdated or incorrect data. For this, you use the DELETE
statement.
WHERE
Clause is not mandatory in DELETE
statement. But without the WHERE
clause you will end up removing all the rows from the table.
Syntax:
Example: Removing a Discontinued Product
Let’s say the “Wireless Mouse” is no longer sold. Here’s how you can remove it from the Products table:
Result: The “Wireless Mouse” is gone from the table.
Scenario: Managing Inventory
Let’s tie everything together with a realistic example:
- Adding a new product:
You receive a new product, “Treadmill,” and want to add it to the database:
- Updating existing stock:
A shipment of “Bluetooth Speakers” arrives, so you increase their stock by 10 units:
- Removing a discontinued product:
The “Smartwatch” is discontinued, so you delete it from the table:
These commands together allow you to fully manage the data in your tables.
Summary
- Use
INSERT
to add new rows to your tables. - Use
UPDATE
to modify existing data, but always double-check yourWHERE
clause to avoid accidental updates. - Use
DELETE
to remove rows, but be cautious—deleting without aWHERE
clause removes all rows in the table.
What’s Next?
Now that you’ve mastered modifying data, the next step is to learn about creating and altering tables. Stay tuned!