🗄️ SQL Lesson 02 — INSERT / UPDATE / DELETE

1. INSERT INTO — Data ထည့်ယူ

-- Insert one row
INSERT INTO users (name, age, city, score, active)
VALUES ('Ko Zin', 30, 'Yangon', 88, 1);

-- Insert multiple rows at once
INSERT INTO users (name, age, city, score, active)
VALUES
    ('Ma Win', 24, 'Mandalay', 79, 1),
    ('Ko Htun', 31, 'Bago', 82, 1),
    ('Ma Su', 19, 'Yangon', 94, 1);

-- Insert with SELECT (copy data)
INSERT INTO top_users (name, score)
SELECT name, score FROM users WHERE score >= 90;

-- Note: id is AUTO_INCREMENT — don't include it!
-- auto_increment: 1, 2, 3, 4... automatically

2. UPDATE — Data ပြောင်း

-- Update one column
UPDATE users
SET age = 26
WHERE id = 1;

-- Update multiple columns
UPDATE users
SET age = 26,
    city = 'Mandalay',
    score = 98
WHERE id = 1;

-- Update with expression
UPDATE users
SET score = score + 5   -- add 5 to all scores
WHERE active = 1;

-- Update multiple rows matching condition
UPDATE users
SET active = 0
WHERE score < 70;

3. DELETE — Data ဖျက်

-- Delete specific row
DELETE FROM users WHERE id = 5;

-- Delete multiple rows
DELETE FROM users WHERE active = 0;

-- Delete with LIMIT
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;

-- Delete all rows (keep structure)
DELETE FROM users;

-- TRUNCATE — faster way to empty table, resets AUTO_INCREMENT
TRUNCATE TABLE users;

4. Safe Practice Pattern

-- ✅ BEST PRACTICE: SELECT စစ်ကြည့်ပြီးမှ DELETE/UPDATE

-- Step 1: SELECT ကြည့်
SELECT * FROM users WHERE active = 0;
-- မှန်ရင် Step 2

-- Step 2: DELETE
DELETE FROM users WHERE active = 0;

-- ====================================
-- Transactions — error ဖြစ်ရင် rollback
START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;    -- ✅ save changes
-- or
ROLLBACK;  -- ❌ undo all changes

5. CRUD Summary