UPDATE va DELETE

UPDATE jadval
SET ustun1 = qiymat1, ustun2 = qiymat2
WHERE shart;

-- Bitta talabaning bahosini yangilash
UPDATE talabalar
SET ortacha_baho = 4.75
WHERE id = 3;

UPDATE talabalar
SET
    telefon = '+998901112233',
    shahar  = 'Toshkent',
    yangilangan = NOW()
WHERE id = 5;

-- Barcha baholarni 0.1 ga oshirish
UPDATE talabalar
SET ortacha_baho = ortacha_baho + 0.1
WHERE ortacha_baho < 4.9;

-- Narxlarni 15% ga oshirish
UPDATE mahsulotlar
SET narx = ROUND(narx * 1.15)
WHERE kategoriya = 'elektronika';

-- Hisoblagichni oshirish
UPDATE maqolalar
SET korishlar = korishlar + 1
WHERE id = 42;

-- Avval: nima o'zgaradi?
SELECT id, ism, ortacha_baho FROM talabalar WHERE shahar = 'Toshkent';

-- Natija to'g'ri bo'lsa, keyin:
UPDATE talabalar SET ortacha_baho = ortacha_baho + 0.2 WHERE shahar = 'Toshkent';

SET SQL_SAFE_UPDATES = 1;

UPDATE talabalar SET ortacha_baho = 5.0;

UPDATE talabalar
SET ortacha_baho = 4.5
WHERE shahar = 'Toshkent'
LIMIT 1;

START TRANSACTION;

UPDATE talabalar SET ortacha_baho = ortacha_baho + 0.5;

SELECT ism, ortacha_baho FROM talabalar;    -- tekshiramiz

ROLLBACK;    -- yoqmasa bekor qilamiz
-- yoki
COMMIT;      -- to'g'ri bo'lsa tasdiqlaymiz

-- CASE bilan
UPDATE talabalar
SET daraja = CASE
    WHEN ortacha_baho >= 4.5 THEN 'alochi'
    WHEN ortacha_baho >= 3.5 THEN 'yaxshi'
    ELSE 'oddiy'
END;

-- Bir nechta qatorni har xil qiymat bilan
UPDATE mahsulotlar
SET narx = CASE id
    WHEN 1 THEN 185000
    WHEN 2 THEN  95000
    WHEN 3 THEN 2450000
END
WHERE id IN (1, 2, 3);

-- JOIN bilan (11-bo'limda batafsil)
UPDATE talabalar t
JOIN guruhlar g ON g.id = t.guruh_id
SET t.faolmi = FALSE
WHERE g.yil < 2025;

-- Ichki so'rov bilan
UPDATE guruhlar g
SET talabalar_soni = (
    SELECT COUNT(*) FROM talabalar t WHERE t.guruh_id = g.id
);

DELETE FROM jadval WHERE shart;

-- Bitta qatorni o'chirish
DELETE FROM talabalar WHERE id = 8;

-- Shart bo'yicha
DELETE FROM talabalar WHERE faolmi = FALSE;

-- Eski yozuvlarni tozalash
DELETE FROM loglar WHERE yaratilgan < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Cheklov bilan
DELETE FROM loglar
WHERE yaratilgan < '2025-01-01'
ORDER BY yaratilgan
LIMIT 1000;

DELETE FROM talabalar;        -- qatorlar o'chadi, AUTO_INCREMENT saqlanadi
TRUNCATE TABLE talabalar;     -- qatorlar o'chadi, AUTO_INCREMENT 1 ga qaytadi
DROP TABLE talabalar;         -- jadval umuman yo'qoladi

-- Jadvalga ustun qo'shamiz
ALTER TABLE talabalar ADD COLUMN ochirilgan_sana DATETIME NULL DEFAULT NULL;

-- "O'chirish" - aslida belgilash
UPDATE talabalar SET ochirilgan_sana = NOW() WHERE id = 5;

-- Faol yozuvlarni olish
SELECT * FROM talabalar WHERE ochirilgan_sana IS NULL;

-- Tiklash
UPDATE talabalar SET ochirilgan_sana = NULL WHERE id = 5;

-- 1. Faol bo'lmagan talabalarni arxivga ko'chirish va o'chirish
START TRANSACTION;

INSERT INTO talabalar_arxiv (ism, familiya, email, shahar, arxivlangan)
SELECT ism, familiya, email, shahar, NOW()
FROM talabalar
WHERE faolmi = FALSE;

DELETE FROM talabalar WHERE faolmi = FALSE;

COMMIT;

-- 2. Takrorlanuvchi email larni tozalash (eng eskisini qoldirib)
DELETE t1 FROM talabalar t1
JOIN talabalar t2
  ON t1.email = t2.email AND t1.id > t2.id;

-- 3. Guruh statistikasini yangilash
UPDATE guruhlar g
SET
    talabalar_soni = (SELECT COUNT(*) FROM talabalar t WHERE t.guruh_id = g.id),
    ortacha_baho   = (SELECT ROUND(AVG(ortacha_baho), 2) FROM talabalar t WHERE t.guruh_id = g.id);

-- 4. Narxlarni kategoriya bo'yicha turlicha o'zgartirish
UPDATE mahsulotlar
SET narx = ROUND(
    narx * CASE kategoriya
        WHEN 'elektronika' THEN 1.10
        WHEN 'kiyim'       THEN 1.05
        ELSE 1.00
    END
)
WHERE faolmi = TRUE;

-- 5. Eski loglarni bo'lib-bo'lib o'chirish (katta jadval uchun)
DELETE FROM loglar
WHERE yaratilgan < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 10000;
-- Bu so'rovni ROW_COUNT() = 0 bo'lguncha takrorlash kerak

UPDATE talabalar SET faolmi = FALSE WHERE ortacha_baho < 3.0;
SELECT ROW_COUNT() AS ozgargan_qatorlar;