DML I — INSERT, UPDATE, DELETE

DML — INSERT, UPDATE, DELETE

Read: ~30 min Easy 14 practice problems Live SQLite runner

1. From DDL to DML — Now We Move Data

In Module 11 we built the empty shelves of our database. In this module we put things on the shelves, move them around, and throw them away — using DML (Data Manipulation Language): INSERT, UPDATE and DELETE. These three verbs change the actual rows stored on disk, which means they are also the three statements that can destroy data — so we will also learn how to wrap them in transactions to make every change reversible until we say COMMIT.

১১ নম্বর module-এ আমরা database-এর খালি shelf তৈরি করেছিলাম; এই module-এ সেই shelf-এ জিনিস রাখা, সরানো ও ফেলে দেওয়া শিখব। SQL-এর এই অংশটার নাম DML — Data Manipulation Language। তিনটি প্রধান statement — INSERT, UPDATE, DELETE। এরাই row পরিবর্তন করে, তাই এরাই ডেটা নষ্ট-ও করতে পারে। এই কারণে transaction দিয়ে কীভাবে নিরাপদে কাজ করা যায়, সেটাও দেখব।

Big idea DDL changes structure. DML changes data. Transactions guarantee that a group of DML statements either all happen, or none of them happen.

DDL → গঠন বদলায়। DML → ডেটা বদলায়। Transaction নিশ্চিত করে — একসাথে অনেকগুলো DML হয় সবগুলো হবে, না হলে কোনোটাই হবে না।

2. `INSERT` — Adding Rows

INSERT INTO adds new rows. There are three flavours you will use almost daily: single-row insert, multi-row insert, and INSERT … SELECT. Pick the one that matches your task.

INSERT INTO দিয়ে নতুন row যোগ করা হয়। প্রতিদিনের কাজে তিনটি ধরন ব্যবহার হয় — single row, multi row, এবং অন্য table থেকে এসে INSERT … SELECT। কাজ অনুযায়ী সঠিকটি বেছে নিতে হবে।

2.1 — Single-row insert

insert_one.sql

-- Always list columns explicitly — never rely on column order.
INSERT INTO customers (name, phone, balance)
VALUES ('Rahim Uddin', '01711-100100', 1500.00);

SELECT * FROM customers;

Style rule — always list column names Writing INSERT INTO customers VALUES (…) works only as long as no one ever adds, removes or rearranges a column. Listing names makes your code resilient to schema evolution.

Column-এর নাম না লিখলে কেউ schema পরিবর্তন করার সাথে সাথেই আপনার insert ভেঙে যাবে। তাই সবসময় column-এর নাম লেখা ভালো।

2.2 — Multi-row insert

insert_many.sql

-- One statement, three rows — much faster than three separate inserts.
INSERT INTO products (title, price, stock) VALUES
    ('Walton 1.5T AC',      42500,  8),
    ('Pran Mango Juice 1L', 95,   300),
    ('Square iSpring 5L',    3800,  14);

SELECT * FROM products;

2.3 — `INSERT … SELECT` (copy from another query)

Sometimes the data you want to insert already lives in another table — for example, an archive of last month's bKash transactions. INSERT … SELECT lets you compute the rows on the fly:

insert_select.sql

-- Move only the November rows into the archive table
INSERT INTO bkash_archive_2024_11 (sender, amount, tx_date)
SELECT sender, amount, tx_date
FROM bkash_tx
WHERE tx_date LIKE '2024-11-%';

SELECT * FROM bkash_archive_2024_11;

3. `UPDATE` — Changing Existing Rows

UPDATE changes the values of existing rows. The structure is always UPDATE table SET col = value [, col = value …] WHERE condition. The WHERE clause is critical: omit it and you will update every single row in the table.

UPDATE দিয়ে existing row-এর value পরিবর্তন করা হয়। গঠনটি সবসময় — UPDATE table SET col = value WHERE condition। WHERE ছাড়া UPDATE চালালে সমস্ত row বদলে যাবে — এটিই database history-র সবচেয়ে বিখ্যাত ভুলগুলোর একটি।

update_safe.sql

-- Add 100 BDT to Karim's balance only.
UPDATE customers
SET    balance = balance + 100
WHERE  name = 'Karim';

SELECT * FROM customers;

The "no WHERE" disaster The query UPDATE customers SET balance = 0; sets every customer's balance to zero. This kind of one-character mistake has caused multi-crore losses at real fintech companies. Always preview with a SELECT first.

UPDATE customers SET balance = 0; — এই query সব customer-এর balance শূন্য করে দেবে। বিশ্বের অনেক fintech কোম্পানি ঠিক এই রকম এক অক্ষরের ভুল থেকে কোটি টাকা হারিয়েছে। আগে SELECT দিয়ে দেখুন কোন কোন row পাল্টাবে।

3.1 — Preview-then-update pattern

preview_pattern.sql

-- Step 1 — preview affected rows
SELECT id, title, stock
FROM products
WHERE stock = 0;

-- Step 2 — only after the preview looks right, run the update
UPDATE products
SET    title = title || ' (Out of stock)'
WHERE  stock = 0;

SELECT * FROM products;

4. `DELETE` — Removing Rows

DELETE FROM table WHERE condition removes rows that match the predicate. Like UPDATE, forgetting WHERE wipes the entire table. Unlike DROP, the table itself remains; you can immediately insert new rows.

DELETE দিয়ে নির্দিষ্ট row মুছে ফেলা হয় — table নিজে রয়ে যায়। WHERE ভুলে গেলে পুরো table খালি হয়ে যাবে। তাই এই statement-ও খুব সাবধানে চালাতে হয়।

delete_targeted.sql

-- Remove all failed transactions older than the cleanup threshold.
DELETE FROM bkash_tx
WHERE status = 'failed';

SELECT * FROM bkash_tx;

4.1 — DELETE vs TRUNCATE — a recap

Statement	Filterable?	Triggers fire?	Speed	SQLite support
`DELETE FROM t WHERE …`	Yes	Yes	O(n) over matched rows	Yes
`DELETE FROM t` (no WHERE)	No	Yes (per row)	O(1) optimised in SQLite	Yes — "truncate optimization"
`TRUNCATE TABLE t`	No	Often skipped	O(1)	No — use `DELETE FROM t`

5. `ON CONFLICT` — SQLite UPSERT

What if the row already exists? The classic problem: a mobile-recharge service receives the same callback twice and tries to insert the same transaction id. You want the second attempt to update the row instead of failing. SQLite (and PostgreSQL) solve this with INSERT … ON CONFLICT DO UPDATE, commonly called UPSERT.

যদি row আগেই থাকে — তাহলে কী হবে? উদাহরণ: একটি মোবাইল রিচার্জ সার্ভিস একই callback দুবার পেলো; দ্বিতীয়বার একই transaction id insert করলে error হবে। সমাধান হলো — INSERT … ON CONFLICT DO UPDATE, যাকে UPSERT বলে। প্রথমবার insert হবে, পরবর্তী বার সেই row update হবে।

upsert.sql

-- First webhook arrives — insert the row.
INSERT INTO recharges (tx_id, phone, amount)
VALUES ('TX-2024-0001', '01711-555555', 200)
ON CONFLICT(tx_id) DO UPDATE SET
    attempts = recharges.attempts + 1;

-- Same webhook arrives a second time (network retry).
INSERT INTO recharges (tx_id, phone, amount)
VALUES ('TX-2024-0001', '01711-555555', 200)
ON CONFLICT(tx_id) DO UPDATE SET
    attempts = recharges.attempts + 1;

SELECT * FROM recharges;

You can also write ON CONFLICT(col) DO NOTHING when you simply want to ignore duplicates:

do_nothing.sql

INSERT INTO emails(addr) VALUES ('a@x.com')
    ON CONFLICT(addr) DO NOTHING;

INSERT INTO emails(addr) VALUES ('a@x.com')
    ON CONFLICT(addr) DO NOTHING;

SELECT COUNT(*) AS total FROM emails;

6. Transactions — `BEGIN` / `COMMIT` / `ROLLBACK`

A transaction groups several DML statements into a single all-or-nothing unit. The classic example is a bank transfer: deduct from sender, credit receiver. If the second statement fails, the first one must be undone — otherwise money disappears. SQL gives us three keywords for this:

Keyword	Effect	বাংলায়
`BEGIN` (or `BEGIN TRANSACTION`)	Starts a new transaction.	একটি নতুন transaction শুরু করে।
`COMMIT`	Saves all changes made since BEGIN.	সব পরিবর্তন স্থায়ীভাবে সংরক্ষণ করে।
`ROLLBACK`	Undoes everything since BEGIN.	সব পরিবর্তন বাতিল করে আগের অবস্থায় ফিরিয়ে দেয়।

একটি transaction মানে কয়েকটি DML statement একসাথে — হয় সবগুলো কাজ করবে, না হয় কোনোটাই হবে না। ব্যাংকের টাকা পাঠানো এর সবচেয়ে ভালো উদাহরণ — sender-এর account থেকে কাটা হলো কিন্তু receiver-এ যোগ হওয়ার আগেই কোনো error হলো? তখন ROLLBACK করে সব আগের অবস্থায় ফিরিয়ে দিতে হবে।

Figure 12.1 — A transaction: atomic, consistent, isolated, durable (ACID).

transfer.sql

-- Send 1500 BDT from Rahim to Karim — atomically.
BEGIN;
    UPDATE accounts SET balance = balance - 1500 WHERE owner = 'Rahim';
    UPDATE accounts SET balance = balance + 1500 WHERE owner = 'Karim';
COMMIT;

SELECT * FROM accounts;

And now a transaction we deliberately roll back — note that the balances do not change:

rollback.sql

BEGIN;
    UPDATE accounts SET balance = balance - 9999 WHERE owner = 'Rahim';
    -- Realised the amount is wrong — undo everything.
ROLLBACK;

SELECT * FROM accounts;

7. The Production-Safe DML Checklist

✅ Always do (সবসময় করুন)

List column names in every INSERT.
SELECT first to preview rows you will UPDATE or DELETE.
Wrap related changes in BEGIN … COMMIT.
Use ON CONFLICT for retry-safe INSERTs.
Take a backup before bulk operations.

⚠️ Common DML disasters (বিপদ)

UPDATE t SET col = … with no WHERE.
DELETE FROM t with no WHERE.
Mismatched column count in VALUES.
Trusting a webhook to fire only once (no UPSERT).
Forgetting COMMIT — your changes never become visible to others.

Mantra "Read before you write. Wrap before you change. Commit only when sure."

"Write করার আগে read; পরিবর্তনের আগে wrap; নিশ্চিত হলে commit।"

8. Practice Problems

Each problem has a runnable answer. Try first, then click Show Answer.

প্রথমে নিজে চেষ্টা করুন; পরে Show Answer চেপে কোডটি সরাসরি Run করে দেখুন।

Insert three students (name + email) into a students table in one statement.

এক statement-এ তিনজন student insert করুন।

✨ Show Answer

ans1.sql

INSERT INTO students(name, email) VALUES
    ('Arif',    'arif@nsu.edu.bd'),
    ('Sanjida', 'sanjida@du.ac.bd'),
    ('Tanvir',  'tanvir@buet.ac.bd');
SELECT * FROM students;

Increase every product's price by 10 percent in a products table.

প্রতিটি product-এর price ১০% বাড়ান।
✨ Show Answer
ans2.sql
```
UPDATE products SET price = price * 1.10;
SELECT * FROM products;
```
Delete every order whose status is 'cancelled'.

যেগুলোর status 'cancelled' সেগুলো delete করুন।
✨ Show Answer
ans3.sql
```
DELETE FROM orders WHERE status = 'cancelled';
SELECT * FROM orders;
```
Use INSERT … SELECT to copy all 'success' transactions into a tx_success table.

INSERT … SELECT দিয়ে সব 'success' transaction tx_success table-এ copy করুন।
✨ Show Answer
ans4.sql
```
INSERT INTO tx_success(sender, amount)
SELECT sender, amount FROM bkash_tx WHERE status = 'success';
SELECT * FROM tx_success;
```

UPSERT a row keyed by email: insert if new, otherwise increment a visits counter.

email-কে key ধরে UPSERT লিখুন; নতুন হলে insert, পুরোনো হলে visits বাড়ান।

✨ Show Answer

ans5.sql

INSERT INTO visitors(email) VALUES ('rahim@x.com')
    ON CONFLICT(email) DO UPDATE SET visits = visitors.visits + 1;

INSERT INTO visitors(email) VALUES ('rahim@x.com')
    ON CONFLICT(email) DO UPDATE SET visits = visitors.visits + 1;

SELECT * FROM visitors;

Implement a money transfer of 500 from Account 1 to Account 2 using a transaction.

একটি transaction দিয়ে Account 1 থেকে Account 2-এ ৫০০ টাকা পাঠান।
✨ Show Answer
ans6.sql
```
BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
SELECT * FROM accounts;
```
Show that ROLLBACK undoes an UPDATE.

ROLLBACK যে UPDATE বাতিল করে দেখান।
✨ Show Answer
ans7.sql
```
BEGIN;
  UPDATE t SET x = 9999;
ROLLBACK;
SELECT * FROM t;
```

Use ON CONFLICT DO NOTHING to ignore duplicate phone numbers when bulk-inserting.

Duplicate phone-গুলো এড়িয়ে যাবার জন্য ON CONFLICT DO NOTHING ব্যবহার করুন।

✨ Show Answer

ans8.sql

INSERT INTO contacts(phone, name) VALUES ('01711-1', 'A') ON CONFLICT(phone) DO NOTHING;
INSERT INTO contacts(phone, name) VALUES ('01711-1', 'B') ON CONFLICT(phone) DO NOTHING;
INSERT INTO contacts(phone, name) VALUES ('01911-2', 'C') ON CONFLICT(phone) DO NOTHING;
SELECT * FROM contacts;

Why does the database community say "every UPDATE without WHERE is a bug"? Two sentences.

"WHERE ছাড়া UPDATE সবসময় bug" — কেন? দুই বাক্যে ব্যাখ্যা।

✨ Show Answer

Answer: A WHERE-less UPDATE silently rewrites every row in the table, often touching millions of records that should not change. The intent was almost always "change one specific thing," so the absence of WHERE means the developer skipped saying which thing — a bug by definition.

WHERE-হীন UPDATE পুরো table-এর সব row বদলে দেয়, যেটা সাধারণত উদ্দেশ্য নয়। Developer সাধারণত একটিমাত্র row বদলাতে চান — WHERE না লিখলে সেটা বলা হয়নি, তাই এটা bug।
Increase by 50 the stock of every product whose title starts with 'Pran'.

যাদের title 'Pran' দিয়ে শুরু — তাদের stock ৫০ বাড়ান।
✨ Show Answer
ans10.sql
```
UPDATE products SET stock = stock + 50
WHERE title LIKE 'Pran%';
SELECT * FROM products;
```
Delete the oldest 2 rows from a table by id (use a subquery).

Subquery দিয়ে id অনুযায়ী সবচেয়ে পুরোনো ২টি row delete করুন।
✨ Show Answer
ans11.sql
```
DELETE FROM logs
WHERE id IN (
    SELECT id FROM logs ORDER BY id ASC LIMIT 2
);
SELECT * FROM logs;
```
Insert a row that uses a DEFAULT for one column without naming the value.

একটি column-এর জন্য DEFAULT ব্যবহার করে row insert করুন।
✨ Show Answer
ans12.sql
```
INSERT INTO notes(body) VALUES ('Hello');
SELECT * FROM notes;
```
Use the RETURNING clause (SQLite 3.35+) to get back the row you just inserted.

Insert-এর পর RETURNING দিয়ে নতুন row ফেরত নিন।
✨ Show Answer
ans13.sql
```
INSERT INTO users(name) VALUES ('Tania')
RETURNING id, name;
```
In two sentences, explain why you should always run SELECT before DELETE on production.

Production-এ DELETE-এর আগে কেন SELECT চালাবেন? দুই বাক্যে।

✨ Show Answer

Answer: A SELECT preview shows exactly which rows will be touched, letting you spot a missing or wrong WHERE condition before any data is destroyed. Deleting the wrong rows in production often cannot be undone without restoring from backup, which costs hours and customer trust.

SELECT-এ আগে দেখলে বোঝা যায় ঠিক কোন কোন row delete হবে; ভুল WHERE হলে আগেই ধরা যায়। Production-এ ভুল row delete হলে backup থেকে restore ছাড়া উদ্ধার নেই, যেটা সময় এবং গ্রাহকের বিশ্বাস — দুটোই কাড়ে।

Summary — Module 12

DML changes data: INSERT adds rows, UPDATE changes values, DELETE removes rows. Three habits make these statements safe in production: always list column names in INSERT, always preview UPDATE/DELETE with a SELECT first, and always wrap related changes in BEGIN … COMMIT so a single failure can be undone with ROLLBACK. For idempotent inserts (webhooks, retries, sync jobs) use SQLite's ON CONFLICT to UPSERT instead of failing.

DML দিয়ে ডেটা পরিবর্তন হয় — INSERT, UPDATE, DELETE। নিরাপদে ব্যবহারের তিনটি অভ্যাস — column-এর নাম লেখা, SELECT দিয়ে preview করা, এবং BEGIN/COMMIT-এ মোড়ানো। Webhook ও retry-এর জন্য ON CONFLICT ব্যবহার করুন।

Next Module → SELECT Basics — filter, sort, DISTINCT। যে statement আপনারা সবচেয়ে বেশি লিখবেন।

Previous: Module 11 Next: SELECT Basics

1. From DDL to DML — Now We Move Data

2. INSERT — Adding Rows

2.1 — Single-row insert

2.2 — Multi-row insert

2.3 — INSERT … SELECT (copy from another query)

3. UPDATE — Changing Existing Rows

3.1 — Preview-then-update pattern

4. DELETE — Removing Rows

4.1 — DELETE vs TRUNCATE — a recap

5. ON CONFLICT — SQLite UPSERT

6. Transactions — BEGIN / COMMIT / ROLLBACK

7. The Production-Safe DML Checklist

✅ Always do (সবসময় করুন)

⚠️ Common DML disasters (বিপদ)

8. Practice Problems

Summary — Module 12

2. `INSERT` — Adding Rows

2.3 — `INSERT … SELECT` (copy from another query)

3. `UPDATE` — Changing Existing Rows

4. `DELETE` — Removing Rows

5. `ON CONFLICT` — SQLite UPSERT

6. Transactions — `BEGIN` / `COMMIT` / `ROLLBACK`