結構化查詢語言/UPDATE 2

提示：注意並停用AUTOCOMMIT。

本頁面提供兩種額外技術，作為對UPDATE 命令的擴充套件，該命令顯示在之前頁面之一上

• 計算在執行時分配給列的值。
• 使用複雜的子查詢作為 WHERE 子句中的搜尋條件。

執行時評估值

分配給列的值可以透過相關或不相關的標量值子查詢在相關聯的表或其他表中計算。在許多用例中都使用了這種技術：線性或百分比增加值，使用來自同一表或其他表的 value，...。情況類似於在關於INSERT 命令的頁面中描述的情況。

-- The average weight of all persons is stored in column 'weight' of the first four persons.
UPDATE person SET
  -- dynamic computation of a value
  weight = (SELECT AVG(weight) FROM person)
  --  weight = (SELECT * FROM (SELECT AVG(weight) FROM person) tmp) -- MySQL insists on using an intermediate table
WHERE  id < 5;

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

子查詢可以使用當前正在更新行的值。在下一個示例中，人員會收到其家庭的平均體重。為了計算此平均體重，需要使用實際處理行的“lastname”列。

-- The subquery is a 'correlated' scalar value subquery.
UPDATE person p SET
  -- 'p.lastname' refers to the lastname of the actual row. The subquery bears all rows in mind, not only those with 'id >= 5'.
  weight = (SELECT AVG(weight) FROM person sq WHERE sq.lastname = p.lastname)
  -- A hint to MySQL users: MySQL does not support UPDATE in combination with a correlated subquery
  -- to the same table. Different tables work. MySQL has a different, non-standard concept: multi-table update.
WHERE  id >= 5;

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

WHERE 子句中的子查詢

WHERE 子句確定 UPDATE 命令涉及的表的哪些行。此 WHERE 子句與 SELECT 或 DELETE 命令的 WHERE 子句具有相同的語法和語義。它可能包含布林運算子的複雜組合，如 ANY、ALL 或 EXISTS 等謂詞，以及在SELECT: Subquery中描述的遞迴子查詢。

-- UPDATE rows in the 'person' table based on the results of a subquery to the 'contact' table.
-- In the example, persons with more than 2 contact information are affected.
UPDATE person
SET    firstname = 'Has many buddies'
WHERE  id IN
  (SELECT person_id
   FROM   contact
   GROUP BY person_id
   HAVING count(*) > 2
  );

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

該命令在 person 表中執行 UPDATE 操作，但受影響的行由 contact 表中的子查詢確定。這種從其他表中獲取資訊的技術為修改資料提供了非常靈活的策略。

在子查詢中選擇 0 行不是錯誤。在這種情況下，DBMS 將像往常一樣執行 UPDATE 命令，並且不會丟擲任何異常。（對於 SELECT 或 DELETE 語句中的子查詢，情況也是如此。）

練習

將所有 firstname 小於 5 個字元的 persons 的 firstname 賦值為“Short firstname”。

點選檢視解決方案

-- Hint: Some implementations use a different function name: length() or len().
UPDATE person
SET    firstname = 'Short firstname'
WHERE  character_length(firstname) < 5;

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;

將所有沒有愛好的 persons 的 firstname 賦值為“No hobby”。

點選檢視解決方案

UPDATE person
SET    firstname = 'No hobby'
WHERE  id NOT IN
  (SELECT person_id
   FROM   person_hobby
  );

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;

將所有執行“Underwater Diving”或“Yoga”愛好的 persons 的 firstname 賦值為“Sportsman”。

點選檢視解決方案

UPDATE person
SET    firstname = 'Sportsman'
WHERE  id IN
  -- The subquery must join to the table 'hobby' to see their column 'hobbyname'.
  (SELECT ph.person_id
   FROM   person_hobby ph
   JOIN   hobby        h  ON ph.hobby_id = h.id
   AND    h.hobbyname IN ('Underwater Diving', 'Yoga')
  );

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;