UPDATE 表名 
SET 列1=值1, 列2=值2  -- 修改哪些字段
[WHERE 條件表達式]     -- 關(guān)鍵控制點！
[ORDER BY ...] 
[LIMIT 行數(shù)];

SET SQL_SAFE_UPDATES = 1;  -- 禁止無WHERE的UPDATE

UPDATE orders o
JOIN users u ON o.user_id = u.id 
SET o.status = 'VIP'
WHERE u.level = 'PLATINUM';  -- 更新白金用戶的訂單狀態(tài)

UPDATE products 
SET price = price * 0.9  -- 打9折
WHERE id IN (
  SELECT product_id 
  FROM sales 
  WHERE sale_date < '2025-01-01'
);  -- 僅更新歷史庫存

START TRANSACTION;
UPDATE large_table 
SET flag = 0
WHERE create_time < '2024-01-01' 
LIMIT 1000;  -- 分批次更新避免長事務(wù)鎖
COMMIT;

-- 錯誤示例：把未支付訂單設(shè)為完成，同時清除支付時間
UPDATE orders 
SET status='completed', pay_time=NULL 
WHERE status='unpaid'; -- 導(dǎo)致有效數(shù)據(jù)被破壞！

UPDATE inventory 
SET stock = stock - 1, version = version + 1 
WHERE product_id=1001 AND version=當前版本;

FOREIGN KEY (dept_id) REFERENCES dept(id) 
ON UPDATE CASCADE  -- 級聯(lián)更新

START TRANSACTION;

-- 步驟1：檢查庫存是否充足
SELECT stock INTO @current_stock 
FROM products 
WHERE id=1001 FOR UPDATE; 

-- 步驟2：帶條件更新
UPDATE products 
SET stock = stock - 1 
WHERE id=1001 AND stock >= 1;  -- 防止超賣

-- 步驟3：記錄流水
INSERT INTO inventory_log(product_id, change_num) 
VALUES (1001, -1);

COMMIT;