如何用SQL语句交换Box_Num为1和5的Value字段值？

Hey there! I've got you covered with a concise SQL statement that'll swap the Value fields for Box_Num 1 and 5, regardless of their current values:

UPDATE your_table_name
SET Value = CASE Box_Num
    WHEN 1 THEN (SELECT Value FROM your_table_name WHERE Box_Num = 5)
    WHEN 5 THEN (SELECT Value FROM your_table_name WHERE Box_Num = 1)
    ELSE Value
END
WHERE Box_Num IN (1, 5);

Quick breakdown of how this works: #

• First, replace your_table_name with the actual name of your table!
• The CASE expression dynamically assigns the new Value:
  • For Box_Num = 1, it pulls the Value from the row where Box_Num = 5
  • For Box_Num = 5, it pulls the Value from the row where Box_Num = 1
  • Any other rows (if present) keep their original Value thanks to the ELSE Value clause
• The WHERE clause restricts the update only to the two rows we need, so you don't have to worry about modifying other data by accident

内容的提问来源于stack exchange，提问作者Erik Saini