UPDATE 是修改資料庫現有資料的核心 DML 指令。本文涵蓋標準語法、單/多欄更新、條件篩選、避免常見錯誤與效能建議。
UPDATE table_name
SET column1 = expression1, column2 = expression2, ...
WHERE condition;
UPDATE AntiqueOwners
SET Address = '77, Lincoln St.'
WHERE OwnerFirstName = 'Jane' AND OwnerLastName = 'Akins';
UPDATE AntiqueOwners
SET Address = '77, Lincoln St.',
City = 'Kirkland',
State = 'Washington'
WHERE OwnerFirstName = 'Jane' AND OwnerLastName = 'Akins';
SELECT * FROM table WHERE condition 確認影響範圍,再轉為 UPDATE。UPDATE 回傳影響列數,不回傳資料。需查看變更前後資料請另行 SELECT。SET Price = Price * 1.1 (漲價 10%)。SET Col = DEFAULT、SET Col = NULL (若允許)。TOP (N) (SQL 2005+) 迴圈更新。-- SQL Server 語法
UPDATE t
SET t.ColA = s.ColA, t.ColB = s.ColB
FROM TargetTable t
JOIN SourceTable s ON t.ID = s.ID
WHERE t.Status = 0;
UPDATE Employee
SET Salary = CASE
WHEN Grade = 1 THEN Salary * 1.10
WHEN Grade = 2 THEN Salary * 1.07
ELSE Salary * 1.05 END;
WHERE,先 SELECT 驗證。SET * (不支援)。BEGIN TRAN ... COMMIT/ROLLBACK 確保原子性。OUTPUT inserted.*, deleted.* INTO AuditTable 記錄變更。