sql - 如何交换记录值？

Question

如何将 role = 1 的所有记录更新为值 2 并将 role = 2 的记录更新为值 1 ?

  login   | role
----------+------
 john     |    1
 anne     |    2
 adam     |    1
 robert   |    1
 katie    |    1
 bob      |    2
 tom      |    2
 ...many records with 1 and 2 values

Answer 1

UPDATE table

SET role = CASE role
               WHEN 1 THEN 2
               ELSE 1
           END

WHERE role = 1 OR role = 2;