mysql知道两条数据的id,如何交换它们的某项属性值?

wf2311 发布于 2016/03/03 11:09
阅读 1K+
收藏 0
加载中
0
Sel8616
Sel8616
可以创建临时表,具体记不清,百度
0
新华万千
新华万千
使用后台语言,加个中间变量。如果纯MySQL的话,原理该一样,具体就不知道了
0
maradona
maradona
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

0
Java_Coder
Java_Coder

亲测,可以。。。

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执行后:


Java_Coder
Java_Coder
额,我就是在MySQL里执行的,难道是版本的问题?
wf2311
wf2311
谢谢,但是我在mysql里执行出现错误[Err] 1093 - You can't specify target table 'tb_test' for update in FROM clause,不知道其他数据库怎么样
0
Java_Coder
Java_Coder
试一下这个:
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);



返回顶部
顶部