MYSQL数据库处理相同的数据修改
时间:2007-07-04 来源:x521
我现在有个张表出现了相同的数据.现在想把这个字段其中的一个数据修改下,(在数据后面加的字母什么的),
表如下: mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI| NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.05 sec) 还有其他的字段没建出来. 问题就是这个name字段的,数据有2个活个以上有相同的?现在主要把中一个的值修改下. 如下下面的表中的数据: mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | hwx |
| 2 | hwx |
| 3 | x521 |
| 4 | ccdd |
| 5 | x521 |
+------+------+
4 rows in set (0.02 sec)
================ 要改成下面这样的:
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | hwx |
| 2 | hwx1 |
| 3 | x521 |
| 4 | ccdd |
| 5 | x5211 |
+------+------+
4 rows in set (0.02 sec) 请问下打加这个问题怎么弄? 由于表中的数据比较大,不好手动改.所以请问下。我改怎么弄??? 谢 了。......
========================================
update t1 set name=name+'z' where id in
(SELECT max(id) FROM t1 GROUP BY name HAVING count(name) > 1);
==================================
mysql> update test a,(select max(id) as xid from test group by name
-> having count(name)>1) b set a.name = concat(a.name,'1') where a.id=b.xid; ====================================================================================
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | hwx |
| 2 | hwx |
| 3 | x521 |
| 4 | x521 |
| 5 | aaa |
| 6 | bbb |
| 7 | aaa |
| 8 | ccc |
| 9 | qaz |
| 10 | rrr |
| 11 | nnn |
| 12 | ydb |
| 13 | otf |
| 14 | qaz |
+------+------+
14 rows in set (0.00 sec) mysql> update test a,(select max(id) as xid from test group by name
-> having count(name)>1) b set a.name = concat(a.name,'1') where a.id=b.xid
-> ;
Query OK, 4 rows affected (0.13 sec)
Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| 1 | hwx |
| 2 | hwx1 |
| 3 | x521 |
| 4 | x5211 |
| 5 | aaa |
| 6 | bbb |
| 7 | aaa1 |
| 8 | ccc |
| 9 | qaz |
| 10 | rrr |
| 11 | nnn |
| 12 | ydb |
| 13 | otf |
| 14 | qaz1 |
+------+-------+
14 rows in set (0.02 sec)
=============================
显示相同的数据
============================== mysql> select * from bbs where concat(name) in
-> (select concat(name) from bbs group by name having count(name)>1);
=========================== mysql> select uid,count(my_url),my_url from web_user_info_myurl group by my_url
having count(my_url)>1; ===========================
mysql> select id,name from bbs group by name having count(name)>1;
表如下: mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI| NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.05 sec) 还有其他的字段没建出来. 问题就是这个name字段的,数据有2个活个以上有相同的?现在主要把中一个的值修改下. 如下下面的表中的数据: mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | hwx |
| 2 | hwx |
| 3 | x521 |
| 4 | ccdd |
| 5 | x521 |
+------+------+
4 rows in set (0.02 sec)
================ 要改成下面这样的:
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | hwx |
| 2 | hwx1 |
| 3 | x521 |
| 4 | ccdd |
| 5 | x5211 |
+------+------+
4 rows in set (0.02 sec) 请问下打加这个问题怎么弄? 由于表中的数据比较大,不好手动改.所以请问下。我改怎么弄??? 谢 了。......
========================================
update t1 set name=name+'z' where id in
(SELECT max(id) FROM t1 GROUP BY name HAVING count(name) > 1);
==================================
mysql> update test a,(select max(id) as xid from test group by name
-> having count(name)>1) b set a.name = concat(a.name,'1') where a.id=b.xid; ====================================================================================
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | hwx |
| 2 | hwx |
| 3 | x521 |
| 4 | x521 |
| 5 | aaa |
| 6 | bbb |
| 7 | aaa |
| 8 | ccc |
| 9 | qaz |
| 10 | rrr |
| 11 | nnn |
| 12 | ydb |
| 13 | otf |
| 14 | qaz |
+------+------+
14 rows in set (0.00 sec) mysql> update test a,(select max(id) as xid from test group by name
-> having count(name)>1) b set a.name = concat(a.name,'1') where a.id=b.xid
-> ;
Query OK, 4 rows affected (0.13 sec)
Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| 1 | hwx |
| 2 | hwx1 |
| 3 | x521 |
| 4 | x5211 |
| 5 | aaa |
| 6 | bbb |
| 7 | aaa1 |
| 8 | ccc |
| 9 | qaz |
| 10 | rrr |
| 11 | nnn |
| 12 | ydb |
| 13 | otf |
| 14 | qaz1 |
+------+-------+
14 rows in set (0.02 sec)
=============================
显示相同的数据
============================== mysql> select * from bbs where concat(name) in
-> (select concat(name) from bbs group by name having count(name)>1);
=========================== mysql> select uid,count(my_url),my_url from web_user_info_myurl group by my_url
having count(my_url)>1; ===========================
mysql> select id,name from bbs group by name having count(name)>1;
相关阅读 更多 +