将面积重复的数据

[jieguo]

表有两个字段 mj是浮点型
id mj
1 12
2 24
3 12
4 5
6 5
7 8

功能就是要将里面mj相同的数据，只保留一条，其他的置为0
变为
id mj
1 12
2 24
3 0
4 5
6 0
7 8

应该怎么写

[Garnett_KG]

UPDATE TableName
SET    mj=ISNULL(b.mj,0)
FROM  TableName a LEFT  JOIN 
      (
      SELECT MIN(id) as MinID,mj
      FROM TableName
      GROUP BY mj
      ) b 
      ON a.ID=b.MinID
      

[一品梅]

update tb set mj=0 from tb,tb a where mj=a.mj and id>a.id;

[jzlzss]

学习

[Herb2]

update a set mj = 0 from tb  a where exists(select 1 from tb where mj = a.mj and id <a.id)

[一品梅]

[Quote=引用 4 楼 Herb2 的回复:]
update a set mj = 0 from tb a where exists(select 1 from tb where mj = a.mj and id <a.id)
[/Quote]