Why Modifying Data Matters
You've created tables and learned to query data. Now comes the critical part: actually changing it safely. In real production systems, one forgotten WHERE clause can wipe out your entire customer database. In this lesson, you'll master INSERT, UPDATE, and DELETE — and more importantly, you'll learn why the WHERE condition is your best friend.
We'll use the QuickCart e-commerce database and all queries have been tested locally.
INSERT — Adding New Data
INSERT adds new rows to a table. The simplest form requires you to specify a value for every column — but smart databases let you skip columns with defaults.
Q1: Insert a Single Customer (with RETURNING)
The RETURNING clause is your secret weapon. Instead of blindly hoping the INSERT worked, it shows you exactly what was inserted:
─────────────┼────────────┼──────────────────────────────
81 | Priya | priya.test@quickcart.in
ON CONFLICT (email) DO NOTHING prevents duplicate emails (Priya's account already exists, so this silently skips). RETURNING shows the inserted customer_id — essential for linking to orders or profiles.
Q2: Insert Multiple Customers (Bulk Operation)
Want to seed your database? Multiple rows in one statement are faster than separate INSERT calls:
Both rows inserted successfully. No RETURNING here — when you don't need immediate confirmation, bulk inserts are lightning-fast.
Q3: Insert Products with RETURNING
New products arrive daily. Let's add fresh inventory to QuickCart with RETURNING so we know their product IDs:
────────────┼──────────────────────────
57 | Basmati Rice 5kg TEST
58 | Fresh Milk 1L TEST
Now your application code can use product_id 57 to link to inventory, pricing, or promotional campaigns.
Q4: Insert an Order with RETURNING
When a customer places an order, you create a record and immediately need the order_id to track it:
──────────┼──────────────────────────
201 | 2024-08-15T10:30:00
UPDATE — Changing Existing Data (Safely!)
UPDATE modifies rows that already exist. Every UPDATE must have a WHERE clause that identifies exactly which rows to change. Without it, you update the entire table.
Q5: Update Stock After a Sale (with WHERE)
A customer buys product #1. Stock decreases:
────────────┼───────────
1 | 61
The WHERE clause WHERE product_id = 1 ensures only one row changes. Without it, every product's stock drops by 1 — disaster.
Q6: Upgrade a Customer to Premium Status
After spending $5,000, customer #4 becomes premium:
─────────────┼────────────┼────────────
4 | Suresh | true
The Dangerous UPDATE (and How to Avoid It)
Every database engineer has nightmares about this scenario. Here's what NOT to do:
UPDATE products SET stock_qty = 0;No WHERE clause. Result: all 500 products instantly show zero stock. Your entire storefront becomes "out of stock." Customers see an empty catalog. Refunds, apologies, emergency calls to your boss at midnight. This is a real $50,000 mistake.
Why Does This Happen?
- Copy-paste errors: You test
UPDATE products SET stock_qty = 0 WHERE product_id = 5, then accidentally delete the WHERE clause before pasting into production. - Late-night coding: Tired developer, fingers slip, send button hit.
- Script accidents: A batch job meant to run on a backup database somehow connects to production.
The Safety Check: Always Preview with SELECT
Before any UPDATE or DELETE, run a SELECT with the identical WHERE clause:
If the SELECT shows nothing, don't run the UPDATE. If it shows 500 rows and you expected 3, stop and debug.
DELETE — Removing Data (Also Safely!)
DELETE removes entire rows. Like UPDATE, it requires a WHERE clause identifying which rows to remove.
Q7: Delete Old Test Products with RETURNING
We added test products earlier (names ending in TEST). Let's clean them up and see what we're removing:
────────────┼───────────────────────────
57 | Basmati Rice 5kg TEST
58 | Fresh Milk 1L TEST
RETURNING shows you exactly what got deleted. If those aren't the products you intended to remove, you'd catch the mistake before commit.
The Dangerous DELETE
DELETE FROM customers;No WHERE clause. Result: all 10,000 customers vanish. Account recovery is a nightmare. Your backup from last week becomes your only hope.