如: Update t1 Set area=Null from table as t1 WHERE not exists(select min(orderid) from table where t1.orderid=orderid group by area)
--看看这个,可能速度更快一些 update a set a.area = Null from table as a inner join table as b on a.orderid=b.orderid where a.area=b.area and a.orderid=min(orderid) update a set a.area = Null from table as a inner join table as b on a.orderid=b.orderid where a.area=b.area and a.class=b.class and a.orderid=min(orderid) update a set a.area = Null from table as a inner join table as b on a.orderid=b.orderid where a.area=b.area and a.class=b.class and a.model=b.model and a.orderid=min(orderid)
update a set area = Null from table a left join( SELECT orderid=MIN(orderid) From table GROUP BY area union SELECT orderid=MIN(orderid) From table GROUP BY area,class union SELECT orderid=MIN(orderid) From table GROUP BY area,class,model )b on a.orderid=b.orderid where b.orderid is null
更新一次肯定比更新三次快, join 肯定比 in快
--没经过测试update a set area=case when b.orderid is null then Null else a.area end, class=case when c.orderid is null then Null else a.class end, model=case when d.orderid is null then Null else a.model end from table a left join( SELECT orderid=MIN(orderid) From table GROUP BY area )b on a.orderid=b.orderid left join( SELECT orderid=MIN(orderid) From table GROUP BY area,class )c on a.orderid=c.orderid left join( SELECT orderid=MIN(orderid) From table GROUP BY area,class,model )d on a.orderid=d.orderid where b.orderid is null or c.orderid is null or d.orderid is null
邹老大的第一次回复修改一下可能好一点. update a set area = Null from table a left join(SELECT orderid=MIN(orderid) From table GROUP BY area)b on a.orderid=b.orderid where b.orderid is nullupdate a set class = Null from table a left join( SELECT orderid=MIN(orderid) From table GROUP BY area union SELECT orderid=MIN(orderid) From table GROUP BY area,class )b on a.orderid=b.orderid where b.orderid is nullupdate a set model = Null from table a left join( SELECT orderid=MIN(orderid) From table GROUP BY area union SELECT orderid=MIN(orderid) From table GROUP BY area,class union SELECT orderid=MIN(orderid) From table GROUP BY area,class,model )b on a.orderid=b.orderid where b.orderid is null
用这个了,太神奇了,谢邹建了.update a set area = Null from table a left join(SELECT orderid=MIN(orderid) From table GROUP BY area)b on a.orderid=b.orderid where b.orderid is nullupdate a set class = Null from table a left join(SELECT orderid=MIN(orderid) From table GROUP BY area,class)b on a.orderid=b.orderid where b.orderid is nullupdate a set model = Null from table a left join(SELECT orderid=MIN(orderid) From table GROUP BY area,class,model )b on a.orderid=b.orderid where b.orderid is null
Update t1 Set area=Null from table as t1
WHERE not exists(select min(orderid)
from table where t1.orderid=orderid group by area)
update a set a.area = Null
from table as a inner join table as b on a.orderid=b.orderid
where a.area=b.area and a.orderid=min(orderid)
update a set a.area = Null
from table as a inner join table as b on a.orderid=b.orderid
where a.area=b.area and a.class=b.class and a.orderid=min(orderid)
update a set a.area = Null
from table as a inner join table as b on a.orderid=b.orderid
where a.area=b.area and a.class=b.class and a.model=b.model and a.orderid=min(orderid)
from table a left join(
SELECT orderid=MIN(orderid) From table GROUP BY area
union
SELECT orderid=MIN(orderid) From table GROUP BY area,class
union
SELECT orderid=MIN(orderid) From table GROUP BY area,class,model
)b on a.orderid=b.orderid
where b.orderid is null
area=case when b.orderid is null then Null else a.area end,
class=case when c.orderid is null then Null else a.class end,
model=case when d.orderid is null then Null else a.model end
from table a
left join(
SELECT orderid=MIN(orderid) From table GROUP BY area
)b on a.orderid=b.orderid
left join(
SELECT orderid=MIN(orderid) From table GROUP BY area,class
)c on a.orderid=c.orderid
left join(
SELECT orderid=MIN(orderid) From table GROUP BY area,class,model
)d on a.orderid=d.orderid
where b.orderid is null or c.orderid is null or d.orderid is null
邹老大的第一次回复修改一下可能好一点.
update a set area = Null
from table a left join(SELECT orderid=MIN(orderid) From table GROUP BY area)b on a.orderid=b.orderid
where b.orderid is nullupdate a set class = Null
from table a left join(
SELECT orderid=MIN(orderid) From table GROUP BY area
union
SELECT orderid=MIN(orderid) From table GROUP BY area,class
)b on a.orderid=b.orderid
where b.orderid is nullupdate a set model = Null
from table a left join(
SELECT orderid=MIN(orderid) From table GROUP BY area
union
SELECT orderid=MIN(orderid) From table GROUP BY area,class
union
SELECT orderid=MIN(orderid) From table GROUP BY area,class,model
)b on a.orderid=b.orderid
where b.orderid is null
用这个了,太神奇了,谢邹建了.update a set area = Null
from table a left join(SELECT orderid=MIN(orderid) From table GROUP BY area)b on a.orderid=b.orderid
where b.orderid is nullupdate a set class = Null
from table a left join(SELECT orderid=MIN(orderid) From table
GROUP BY area,class)b on a.orderid=b.orderid
where b.orderid is nullupdate a set model = Null
from table a left join(SELECT orderid=MIN(orderid) From table GROUP BY area,class,model
)b on a.orderid=b.orderid
where b.orderid is null