id name
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
6 fffff
7 kkkk
8 fdsf假如我要把name这列所有的字段值随机打乱怎么写,比如id为1的name字段值被改成id为7的kkkk。
sql语句怎么写,谢谢大家,好人一生平安
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
6 fffff
7 kkkk
8 fdsf假如我要把name这列所有的字段值随机打乱怎么写,比如id为1的name字段值被改成id为7的kkkk。
sql语句怎么写,谢谢大家,好人一生平安
然后再导入
CREATE TABLE test(id int primary key, name varchar(10));
insert test values
(1, 'aaa'),
(2, 'bbb'),
(3, 'ccc'),
(4, 'ddd'),
(5, 'eee'),
(6, 'fffff'),
(7, 'kkkk'),
(8, 'fdsf');update test as data, test as ndata,
(
SELECT aa.id, bb.id as nid
FROM( SELECT id, @a:=@a+1 as a FROM(SELECT id FROM test ORDER BY rand()) d, (SELECT @a:=0) a) aa,
( SELECT id, @b:=@b+1 as b FROM test d, (SELECT @b:=0) b ORDER BY d.id ) bb
WHERE aa.a = bb.b
) nid
set data.name=ndata.name
where data.id =nid.id and nid.nid=ndata.id
;
select * from test;