✏️ UPDATE - Ma'lumotlarni yangilash

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

-- Belgili foydalanuvchining yoshini yangilash
UPDATE users SET age = 26 WHERE id = 1;

-- Belgili foydalanuvchining emailini yangilash
UPDATE users SET email = '[email protected]' WHERE name = 'Ali Valiyev';

-- Bir nechta ustunni bir vaqtda yangilash
UPDATE users 
SET age = 27, email = '[email protected]', phone = '+998901234567'
WHERE id = 1;

-- Belgili shart bo'yicha yangilash
UPDATE users 
SET age = age + 1, updated_at = CURRENT_TIMESTAMP
WHERE age < 30;

-- Barcha foydalanuvchilarning statusini yangilash
UPDATE users SET status = 'active';

-- Barcha mahsulotlarning narxini oshirish
UPDATE products SET price = price * 1.1;  -- 10% oshirish

-- 25 yoshdan kichik foydalanuvchilarni yangilash
UPDATE users 
SET status = 'young_user', category = 'junior'
WHERE age < 25;

-- Belgili kategoriyadagi mahsulotlarni yangilash
UPDATE products 
SET price = price * 0.9, discount = 10
WHERE category = 'Electronics' AND price > 100;

-- Murakkab shartlar
UPDATE users 
SET last_login = CURRENT_TIMESTAMP, login_count = login_count + 1
WHERE (age > 18 AND age < 65) OR (status = 'premium');

-- NULL qiymatni o'rnatish
UPDATE users SET phone = NULL WHERE id = 1;

-- NULL qiymatni almashtirish
UPDATE users SET phone = '+998901234567' WHERE phone IS NULL;

-- NULL bo'lmagan qiymatlarni yangilash
UPDATE users SET phone = 'Unknown' WHERE phone IS NOT NULL AND phone = '';

-- Jadvallar
CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    publication_year INTEGER,
    pages INTEGER,
    price REAL,
    category TEXT,
    is_available BOOLEAN DEFAULT 1,
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Ma'lumotlar
INSERT INTO books (title, author, publication_year, pages, price, category) VALUES 
('Python Programming', 'John Doe', 2020, 300, 45.99, 'Programming'),
('JavaScript Guide', 'Jane Smith', 2019, 250, 35.50, 'Programming'),
('History of Uzbekistan', 'Ahmad Karimov', 2018, 400, 25.00, 'History'),
('O''zbek tili', 'Malika Toshmatova', 2021, 200, 15.75, 'Language');

-- Yangilash misollari
-- Belgili kitobning narxini yangilash
UPDATE books SET price = 50.00 WHERE title = 'Python Programming';

-- 2020 yildan keyin nashr etilgan kitoblarning narxini oshirish
UPDATE books 
SET price = price * 1.15, last_updated = CURRENT_TIMESTAMP
WHERE publication_year > 2020;

-- 'Programming' kategoriyasi kitoblarini yangilash
UPDATE books 
SET price = price * 0.9, category = 'Computer Science'
WHERE category = 'Programming';

-- 200 sahifadan ko'p kitoblarni mavjud emas deb belgilash
UPDATE books 
SET is_available = 0, last_updated = CURRENT_TIMESTAMP
WHERE pages > 200;

-- Boshqa jadvaldan ma'lumot olish
UPDATE books 
SET price = (
    SELECT avg_price 
    FROM category_prices 
    WHERE category_prices.category = books.category
)
WHERE books.category IN (SELECT category FROM category_prices);

-- Murakkab yangilash
UPDATE users 
SET last_order_date = (
    SELECT MAX(order_date) 
    FROM orders 
    WHERE orders.user_id = users.id
)
WHERE users.id IN (SELECT user_id FROM orders);

-- Transaction boshlash
BEGIN TRANSACTION;

-- Bir nechta yangilash operatsiyalari
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';
UPDATE products SET is_available = 0 WHERE category = 'Discontinued';
UPDATE orders SET status = 'cancelled' WHERE order_date < '2023-01-01';

-- Barcha operatsiyalar muvaffaqiyatli bo'lsa
COMMIT;

-- Yoki barcha operatsiyalarni bekor qilish
-- ROLLBACK;

-- Yangilangan qatorlar sonini ko'rish
UPDATE users SET status = 'active' WHERE age > 18;
-- SQLite da yangilangan qatorlar soni ko'rsatiladi

-- Yangilashdan oldin tekshirish
SELECT COUNT(*) FROM users WHERE age > 18;  -- Qancha qator yangilanadi

-- Yangilashdan keyin tekshirish
SELECT * FROM users WHERE status = 'active';  -- Yangilangan ma'lumotlar

-- Xato: WHERE shartini unutish
UPDATE users SET status = 'inactive';
-- Bu BARCHA qatorlarni yangilaydi!

-- Xato: NOT NULL constraint
UPDATE users SET name = NULL WHERE id = 1;
-- Xato: NOT NULL constraint failed: users.name

-- Xato: UNIQUE constraint
UPDATE users SET email = '[email protected]' WHERE id = 2;
-- Xato: UNIQUE constraint failed: users.email

-- Xato: CHECK constraint
UPDATE products SET price = -10 WHERE id = 1;
-- Xato: CHECK constraint failed: products

-- Yangilashdan oldin tekshirish
SELECT * FROM users WHERE id = 1;  -- Mavjudligini tekshirish

-- WHERE shartini har doim qo'shing
UPDATE users SET status = 'active' WHERE id = 1;  -- Xavfsiz

-- Constraint-larni tekshirish
UPDATE users SET email = '[email protected]' 
WHERE id = 1 AND '[email protected]' NOT IN (SELECT email FROM users WHERE id != 1);

-- Indekslardan foydalanish
CREATE INDEX idx_users_age ON users(age);
CREATE INDEX idx_products_category ON products(category);

-- Tez ishlaydigan yangilash
UPDATE users SET status = 'active' WHERE age > 25;  -- Indeks ishlatiladi

-- Sekin ishlaydigan yangilash
UPDATE users SET status = 'active' WHERE name LIKE '%Ali%';  -- Indeks ishlatilmaydi

-- E-commerce tizimi
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    category TEXT,
    stock_quantity INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    discount_percent REAL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER,
    quantity INTEGER,
    total_price REAL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'pending',
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Ma'lumotlar
INSERT INTO products (name, price, category, stock_quantity) VALUES 
('Laptop', 1000.00, 'Electronics', 10),
('Phone', 500.00, 'Electronics', 25),
('Book', 20.00, 'Education', 100),
('T-shirt', 30.00, 'Clothing', 50);

-- Yangilash operatsiyalari
-- 1. Barcha elektronika mahsulotlarining narxini 10% oshirish
UPDATE products 
SET price = price * 1.1, updated_at = CURRENT_TIMESTAMP
WHERE category = 'Electronics';

-- 2. 50 dan ko'p zaxira bor mahsulotlarga 5% chegirma
UPDATE products 
SET discount_percent = 5, updated_at = CURRENT_TIMESTAMP
WHERE stock_quantity > 50;

-- 3. 100 dan arzon mahsulotlarni faol emas deb belgilash
UPDATE products 
SET is_active = 0, updated_at = CURRENT_TIMESTAMP
WHERE price < 100;

-- 4. Buyurtma statusini yangilash
UPDATE orders 
SET status = 'completed', total_price = (
    SELECT p.price * o.quantity 
    FROM products p 
    WHERE p.id = o.product_id
)
WHERE status = 'pending' AND order_date < '2023-01-01';

-- Natijalarni tekshirish
SELECT * FROM products WHERE category = 'Electronics';
SELECT * FROM products WHERE discount_percent > 0;
SELECT * FROM products WHERE is_active = 0;
SELECT * FROM orders WHERE status = 'completed';