mysql - 我想交换数据用例

Question

 UPDATE tb_test 
    set name =  
    ( CASE  
    WHEN (id = 1) THEN (select DISTINCT name from tb_test where id = 2) 
    WHEN (

id = 2) THEN (select DISTINCT name from tb_test where id = 1) 
END )

结果:

[SQL] 更新 tb_test

set name =  
( CASE  
WHEN (id = 1) THEN (select DISTINCT name from tb_test where id = 2) 
WHEN (id = 2) THEN (select DISTINCT name from tb_test where id = 1) 
END )

[Err] 1093 - 您无法在 FROM 子句中指定要更新的目标表“tb_test”

Answer 1

这对你有用

  UPDATE tb_test
  set name =
    ( 
    CASE
         WHEN id = 1
         THEN (select * from (select DISTINCT name from tb_test where id = 2)t)
         WHEN id = 2
         THEN (select * from (select DISTINCT name from tb_test where id = 1)y) 
    END 
   );