MySQL Update 多表时遇到的一个疑问，关于ID重复的

因为tb1.id等于tb2.tid的记录只有一条啊。所以你更新tb1表时只会更新一条。

如果你要的是tb2.tid所有记录的points之和，建议分成两条语句。

先取tb2.points和值：

select sum(points) from tb2 where tid= ...

再更新tb1

update tb1 set points = ... where id = ...

如果是要对tb2中所有记录作处理，

建议先建临时表，

CREATE TEMPORARY TABLE tb3 as select sum(points) point_total, id from tb2 group by id

再用更新语句

update tb1 t, tb3 c set t.points = t.points + c.point_total where t.id = c.id

记得执行成功后要删除临时表

drop table tb3

在网上查找删除重复数据保留id最小的数据，方法如下：

DELETE

FROM

people

WHERE

peopleName

IN

(

SELECT

peopleName

FROM

people

GROUP

BY

peopleName

HAVING

count(peopleName)

>

1

)

AND

peopleId

NOT

IN

(

SELECT

min(peopleId)

FROM

people

GROUP

BY

peopleName

HAVING

count(peopleName)

>

1

)

自己使用的时候显示报错：

delete

from

tb

where

id

in

(SELECT

max(id)

from

tb

GROUP

BY

user

HAVING

count(user)>1)

[Err]

1093

-

You

can't

specify

target

table

‘XXX'

for

update

in

FROM

clause

暂时不知道是什么原因导致的。

然后想办法分布 *** 作，首先筛选出有重复user的数据，然后用max()选出其中较大的那一行：

SELECT

max(id)

from

tb

GROUP

BY

user

HAVING

count(user)>1

然后再根据得到的max(id)逐条删除多余的数据

delete

from

tb

where

id=xx

是个笨方法，暂时先解决问题吧。

总结

以上所述是小编给大家介绍的Mysql删除重复数据保留最小的id

的解决方法，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！

select * from 表 where n_id in

(select s_name from 表

group by s_name,n_money,s_zhipiao,s_date,s_lixi

having count(s_name)>1)

---