文章详情

  • 游戏榜单
  • 软件榜单
关闭导航
热搜榜
热门下载
热门标签
php爱好者> php文档>MYSQL数据库处理相同的数据修改

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;
相关阅读 更多 +
排行榜 更多 +
找茬脑洞的世界安卓版

找茬脑洞的世界安卓版

休闲益智 下载
滑板英雄跑酷2手游

滑板英雄跑酷2手游

休闲益智 下载
披萨对对看下载

披萨对对看下载

休闲益智 下载