如何在同一表中的角色之间交换值

Question

我有表：

ID    ROLE    NAME    TITLE
--    ----    ----    -----
10    DEF     John    Manager
11    UPD     Sam     Engineer
12    DEF     Jill    Lead
13    UPD     Ray     Engneer

我需要交换值并获得结果：

ID    ROLE    NAME    TITLE
--    ----    ----    -----
10    DEF     Sam     Engineer
11    UPD     John    Manager
12    DEF     Ray     Engineer
13    UPD     Jill    Lead

如何用oracle 11g做这个技巧谢谢。

Answer 1

UPDATE the_table dst
FROM the_table src
SET dst.name = src.name
        , dst.title = src.title
WHERE dst.id IN (10,11)
AND src.id IN (10,11)
AND src.id <> dst.id
        ;