在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。