在UPDATE中更新TOP条数据以及UPDATE更新中使用ORDER BY

正常查询语句中TOP的运用:
SELECT TOP 1000 *
FROM MP_MemberGrade
 
随意更新一张表中满足条件的前N条数据:
UPDATE TOP (1) MP_Member SET FKGradeID=2
WHERE IsDeleted=2
 
更新A表中满足B表前N条记录的数据
UPDATE MP_Member SET FKGradeID=2
FROM MP_Member A
LEFT JOIN (SELECT TOP 10 * FROM MP_MemberGrade WHERE IsDeleted=2) B ON A.FKGradeID=B.PKID
WHERE A.IsDeleted=2
UPDATE MP_Member SET FKGradeID=4
FROM (SELECT TOP 10 * FROM MP_MemberGrade WHERE IsDeleted=2) B
WHERE MP_Member.IsDeleted=2 AND FKGradeID=B.PKID
 
UPDATE更新中使用ORDER BY
UPDATE MP_Member SET FKGradeID=2
FROM MP_Member A
LEFT JOIN (SELECT TOP 10 * FROM MP_MemberGrade WHERE IsDeleted=2 ORDER BY PKID DESC) B ON A.FKGradeID=B.PKID
WHERE A.IsDeleted=2
ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效,除非另外还指定了 TOP 或 FOR XML。
 
posted @ 2016-12-20 19:00  流空-C  阅读(7476)  评论(1编辑  收藏  举报