我想交换数据用例的时候(i want to exchange data use case when)

我想交换数据用例的时候(i want to exchange data use case when)

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] 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 )

[Err] 1093 - You can't specify target table 'tb_test' for update in FROM clause

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 )

Result:

[SQL] 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 )

[Err] 1093 - You can't specify target table 'tb_test' for update in FROM clause

最满意答案

它会对你有用

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 );

it will work for you

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 );