INSERT, UPDATE, DELETE আর আপসার্ট, নিরাপদে

INSERT INTO customers (customer_id, name, email, city, joined_on)
VALUES (9,  'Rina Begum',   'rina@example.com',   'Dhaka',   '2026-06-20'),
       (10, 'Arif Mollah',  'arif@example.com',   NULL,      '2026-06-21');

INSERT INTO categories (name) VALUES ('Software');      -- category_id নিজে থেকেই বসে

SELECT customer_id, name, city FROM customers WHERE customer_id >= 9 ORDER BY customer_id;
SELECT * FROM categories ORDER BY category_id;

+-------------+-------------+-------+
| customer_id | name        | city  |
+-------------+-------------+-------+
|           9 | Rina Begum  | Dhaka |
|          10 | Arif Mollah | NULL  |
+-------------+-------------+-------+
+-------------+-------------+
| category_id | name        |
+-------------+-------------+
|           1 | Books       |
|           2 | Electronics |
|           3 | Courses     |
|           4 | Accessories |
|           5 | Furniture   |
|           6 | Software    |
+-------------+-------------+

INSERT INTO customers VALUES (11, 'Joy Saha', 'joy@example.com', '2026-06-22');

Error: table customers has 5 columns but 4 values were supplied

INSERT INTO products (name, category_id, price, stock)
VALUES ('Data Science Handbook', 1, 1800, 20)
RETURNING product_id, name, price;

+------------+-----------------------+-------+
| product_id | name                  | price |
+------------+-----------------------+-------+
|         12 | Data Science Handbook |  1800 |
+------------+-----------------------+-------+

CREATE TABLE restock_requests (
    product_id INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    order_qty  INTEGER NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

INSERT INTO restock_requests (product_id, name, order_qty)
SELECT product_id, name, 20 - stock
FROM products
WHERE stock < 10;

SELECT * FROM restock_requests ORDER BY product_id;

+------------+-----------------------------+-----------+
| product_id | name                        | order_qty |
+------------+-----------------------------+-----------+
|          5 | 27-inch Monitor             |        15 |
|          9 | USB-C Hub                   |        20 |
|         10 | Noise-Cancelling Headphones |        12 |
+------------+-----------------------------+-----------+

UPDATE products
SET stock = stock + 20, price = 2100
WHERE product_id = 9;

SELECT product_id, name, price, stock FROM products WHERE product_id = 9;

+------------+-----------+-------+-------+
| product_id | name      | price | stock |
+------------+-----------+-------+-------+
|          9 | USB-C Hub |  2100 |    20 |
+------------+-----------+-------+-------+

UPDATE orders SET status = 'returned' WHERE order_id = 14;

Error: CHECK constraint failed: status IN ('pending', 'shipped', 'delivered', 'cancelled')

DELETE FROM customers WHERE customer_id = 1;

Error: FOREIGN KEY constraint failed

-- PostgreSQL
DELETE FROM customers WHERE customer_id = 1;

Error: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"

DELETE FROM customers WHERE customer_id = 10;
SELECT COUNT(*) AS customers FROM customers;

+-----------+
| customers |
+-----------+
|         9 |
+-----------+

INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (15, 2, '2026-06-15', 'pending'),
       (16, 3, '2026-06-16', 'returned');

Error: CHECK constraint failed: status IN ('pending', 'shipped', 'delivered', 'cancelled')

SELECT COUNT(*) AS orders FROM orders;

+--------+
| orders |
+--------+
|     14 |
+--------+

BEGIN;
UPDATE products SET stock = 0;              -- ওহো: লিখতে চেয়েছিলাম WHERE product_id = 10
SELECT changes() AS rows_changed;
ROLLBACK;                                   -- 12টা সারি তো পরিকল্পনায় ছিল না: বাতিল

SELECT product_id, stock FROM products WHERE product_id IN (1, 2, 3) ORDER BY product_id;

+--------------+
| rows_changed |
+--------------+
|           12 |
+--------------+
+------------+-------+
| product_id | stock |
+------------+-------+
|          1 |    40 |
|          2 |    15 |
|          3 |    12 |
+------------+-------+

-- MySQL
SET SQL_SAFE_UPDATES = 1;
UPDATE products SET stock = 0;

Error: ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

import sqlite3
from sqlhelp import con

backup = sqlite3.connect("shop-backup.db")
con.backup(backup)                       # shop.db-এর পুরো, ঠিকঠাক একটা কপি
n = backup.execute("SELECT COUNT(*) FROM products").fetchone()[0]
print("products in backup:", n)
backup.close()

products in backup: 12

INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (9,  'USB-C Hub', 4, 2200, 30),
       (13, 'Webcam',    2, 3500, 10)
ON CONFLICT (product_id) DO UPDATE SET stock = excluded.stock
RETURNING product_id, name, price, stock;

+------------+-----------+-------+-------+
| product_id | name      | price | stock |
+------------+-----------+-------+-------+
|          9 | USB-C Hub |  2100 |    30 |
|         13 | Webcam    |  3500 |    10 |
+------------+-----------+-------+-------+

-- PostgreSQL
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (9,  'USB-C Hub', 4, 2200, 30),
       (13, 'Webcam',    2, 3500, 10)
ON CONFLICT (product_id) DO UPDATE SET stock = EXCLUDED.stock
RETURNING product_id, name, price, stock;

+------------+-----------+-------+-------+
| product_id | name      | price | stock |
+------------+-----------+-------+-------+
|          9 | USB-C Hub |  2200 |    30 |
|         13 | Webcam    |  3500 |    10 |
+------------+-----------+-------+-------+

-- MySQL (8.0.19+: AS দিয়ে নতুন সারির নাম দিন)
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (9,  'USB-C Hub', 4, 2200, 30),
       (13, 'Webcam',    2, 3500, 10) AS new
ON DUPLICATE KEY UPDATE stock = new.stock;

SELECT product_id, name, price, stock FROM products WHERE product_id IN (9, 13) ORDER BY product_id;

+------------+-----------+-------+-------+
| product_id | name      | price | stock |
+------------+-----------+-------+-------+
|          9 | USB-C Hub |  2200 |    30 |
|         13 | Webcam    |  3500 |    10 |
+------------+-----------+-------+-------+

INSERT OR REPLACE INTO products (product_id, name, price) VALUES (4, 'Wireless Mouse', 950);
SELECT product_id, name, category_id, price, stock FROM products WHERE product_id = 4;

+------------+----------------+-------------+-------+-------+
| product_id | name           | category_id | price | stock |
+------------+----------------+-------------+-------+-------+
|          4 | Wireless Mouse | NULL        |   950 | NULL  |
+------------+----------------+-------------+-------+-------+

from sqlhelp import con, reset

con.execute("DROP TABLE IF EXISTS restock_requests")   # আমাদের বানানো টেবিল, products-কে রেফার করে
reset()                                                # শপের টেবিলগুলো শুরুর সারিতে ফিরে যায়
print(con.execute("SELECT category_id, stock FROM products WHERE product_id = 4").fetchone())

(2, 60)

from sqlhelp import con, show

new_customers = [
    (9,  "Rina Begum",  "rina@example.com", "Dhaka",    "2026-06-20"),
    (10, "Arif Mollah", "arif@example.com", "Barishal", "2026-06-21"),
]
con.executemany(
    "INSERT INTO customers (customer_id, name, email, city, joined_on) VALUES (?, ?, ?, ?, ?)",
    new_customers,
)

cur = con.execute("UPDATE products SET price = price + ? WHERE category_id = ?", (100, 4))
print("rows updated:", cur.rowcount)
con.commit()                                   # বদলগুলো স্থায়ী করুন

show("SELECT customer_id, name, city FROM customers WHERE customer_id >= 9 ORDER BY customer_id")

rows updated: 2
+-------------+-------------+----------+
| customer_id | name        | city     |
+-------------+-------------+----------+
|           9 | Rina Begum  | Dhaka    |
|          10 | Arif Mollah | Barishal |
+-------------+-------------+----------+

-- সহজ
INSERT INTO categories (category_id, name) VALUES (6, 'Software');
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (12, 'PyCharm Licence', 6, 9000, NULL);
SELECT * FROM products WHERE product_id = 12;

+------------+-----------------+-------------+-------+-------+
| product_id | name            | category_id | price | stock |
+------------+-----------------+-------------+-------+-------+
|         12 | PyCharm Licence |           6 |  9000 | NULL  |
+------------+-----------------+-------------+-------+-------+

-- মাঝারি
SELECT COUNT(*) AS books FROM products WHERE category_id = 1;   -- আশা করছি 2

BEGIN;
UPDATE products SET price = price * 110 / 100 WHERE category_id = 1;
SELECT changes() AS rows_changed;                               -- 2: মিলেছে, তাই কমিট
COMMIT;

SELECT product_id, name, price FROM products WHERE category_id = 1 ORDER BY product_id;

+-------+
| books |
+-------+
|     2 |
+-------+
+--------------+
| rows_changed |
+--------------+
|            2 |
+--------------+
+------------+---------------------------+-------+
| product_id | name                      | price |
+------------+---------------------------+-------+
|          1 | Python Crash Course       |  1320 |
|          2 | Hands-On Machine Learning |  2750 |
+------------+---------------------------+-------+

-- কঠিন
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (3,  'Mechanical Keyboard', 2, 4500, 7),
       (13, 'Graphics Tablet',     2, 6500, 4)
ON CONFLICT (product_id) DO UPDATE SET stock = excluded.stock, price = excluded.price;

SELECT COUNT(*) AS to_delete FROM products WHERE product_id = 12;   -- আগে যাচাই: 1টা সারি
BEGIN;
DELETE FROM products WHERE product_id = 12;
SELECT changes() AS rows_deleted;
COMMIT;

SELECT product_id, name, price, stock FROM products WHERE product_id IN (3, 12, 13) ORDER BY product_id;

+-----------+
| to_delete |
+-----------+
|         1 |
+-----------+
+--------------+
| rows_deleted |
+--------------+
|            1 |
+--------------+
+------------+---------------------+-------+-------+
| product_id | name                | price | stock |
+------------+---------------------+-------+-------+
|          3 | Mechanical Keyboard |  4500 |     7 |
|         13 | Graphics Tablet     |  6500 |     4 |
+------------+---------------------+-------+-------+