SQL 2000 查询条件

来源：百度知道 编辑：UC知道 时间：2024/07/07 01:39:50
数据库ck,表_name_1,其中有列materialid、XH、BJ等等。
materialid列有重复字段。XH列标记的是materialid里重复字段的序号。
现在：在重复字段materialid后边的XH(序号)列值最大的那条记录，如何在其BJ列值标记M ?
在线，如问题描述不明白，提出来我补充。

你这个问题我不是回复了吗？

update _name_1 set BJ = 'M'
where key in
(
select a.key from _name_1 a,
(
select max(XH) XH, materialid from _name_1 where materialid in
(
select materialid from _name_1 group by materialid having count(1) > 1
)
group by materialid
) b
where a.XH=b.XH and a.materialid=b.materialid
)

如果是SQL SERVER，可以写成：
update _name_1 set BJ = 'M'
from
(
select max(XH) XH, materialid from _name_1 where materialid in
(
select materialid from _name_1 group by materialid having count(1) > 1
)
group by materialid
) b
where _name_1.XH=b.XH and _name_1.materialid=b.materialid