5
回答
mysql知道两条数据的id,如何交换它们的某项属性值?
华为云4核8G,高性能云服务器,免费试用   

<无详细内容>

举报
wf2311
发帖于2年前 5回/856阅
共有5个答案 最后回答: 2年前
update you_table s set s.rep_col = 
replace((select replace(group_concat(t.rep_col),',','') from you_table t
 where t.id in (id1,id2)),s.rep_col,'')

where s.id in (id1,id2)

没测试,你可以尝试下,如果两个字段相等会有bug

亲测,可以。。。

1.sql执行之前:

2.sql:

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



3.sql执行后:


--- 共有 2 条评论 ---
Java_Coder额,我就是在MySQL里执行的,难道是版本的问题? 2年前 回复
wf2311谢谢,但是我在mysql里执行出现错误[Err] 1093 - You can't specify target table 'tb_test' for update in FROM clause,不知道其他数据库怎么样 2年前 回复
试一下这个:
UPDATE tb_test 
 set name =  
 (CASE  
    WHEN id = 1 THEN (select sub_query1.new_name from (select DISTINCT name as new_name from tb_test where id = 2 limit 1) as sub_query1 )   
    WHEN id = 2 THEN (select sub_query2.new_name from (select DISTINCT name as new_name from tb_test where id = 1 limit 1) as sub_query2) 
END);



顶部