一般欄位指定及欄位計算
直接指定值與使用簡單的計算，選取demodb資料庫後，滑鼠右鍵點擊新增查詢後，輸入以下SQL 指令後，按下執行:
-- 取出所有採購單明細
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
--注意一定要有 Where 條件
UPDATE PurchasesDetail SET Qty = 6 , Amount = 480 WHERE Id = 1
UPDATE PurchasesDetail SET Qty = Qty + 2 , Amount = 800 WHERE Id = 2
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo執行指令一:
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
執行全部指令
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
UPDATE PurchasesDetail SET Qty = 6 , Amount = 480 WHERE Id = 1
UPDATE PurchasesDetail SET Qty = Qty + 2 , Amount = 800 WHERE Id = 2
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
2.指定複合運算子
複合運算子即為 +=、*=、-=、/= … 等運算子。
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
UPDATE PurchasesDetail SET Qty *= 2 , Amount = 960 WHERE Id = 1
UPDATE PurchasesDetail SET Qty += 4 , Amount = 1200 WHERE Id = 2
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo執行指令一:
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
執行全部指令
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
UPDATE PurchasesDetail SET Qty *= 2 , Amount = 960 WHERE Id = 1
UPDATE PurchasesDetail SET Qty += 4 , Amount = 1200 WHERE Id = 2
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
3.使用 DEFAULT 值更新資料列
使用關鍵字 DEFAULT 來設定某欄位值。
查詢資料表PurchasesDetail 資料結構得知欄位Qty 的預設值為數值1
希望把Qty 的值回復到原本預設值 1，可以執行以下指令
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo
UPDATE PurchasesDetail SET Qty = DEFAULT , Amount = 80 WHERE Id = 1
SELECT * FROM PurchasesDetail ORDER BY ParentId , ProductNo