none
为何update有top却没有order by RRS feed

  • 问题

  • 为何update有top却不能用order by?,真是郁闷

    update top (1) B set ...=... where A_Id=1 order by Sort desc


    BID A_ID Sort
    1     1       1
    2     1       2
    3     1       3
    2009年12月1日 5:27

答案

  • ;with Cte
    as
    (select *,Row=Row_Number()over(Order by Sort desc) from B where A_ID=1)
    
    update Cte
    set .........
    where Row=1
    這樣用,update\Delete用top 1時要小心是采用的是表排序,曾在SQL05環境下測過,剛在SQL08環境測了一下同SQL05一樣。
    結論:樓主最好不要用top 1做為更新判斷
    ROY WU(吳熹 )
    2009年12月1日 7:09
    版主
  • use Tempdb
    go
    create table T(ID int primary  key Clustered,ID2 int,ID3 int)
    
    create index IX_T on T(ID2 asc,ID3 asc)--這里不加上ID3時,表順序會采用聚集索引
    
    insert t(ID,ID2)
    select
        Top 100
        ID=Row_Number()over(order by a.ID),
        ID2=101-Row_Number()over(order by a.ID)
    from syscolumns a,syscolumns b,syscolumns c
    
    
    update top (1) T set ID2=ID2*1000    --更新
    
    select * from T                        --查看結果(ID=100物理順序最后一條/等同ID2=1同表順序的第一條)更新了
    
    delete top (1) T                    --刪除
    
    select * from T                        --查看結果ID2=2表順序的第一條刪除了
    
    
    drop table T
    
    --個人建議:Top做判斷更新和刪除時,不能很好的判斷出表的順序,建議用CTE判斷替換,如表的數據密度、索引、數據量等都是表順序的判斷條件
    貼一下測試的例子給大家參照

    ROY WU(吳熹 )
    • 已标记为答案 SQL STUDIO 2009年12月1日 8:39
    2009年12月1日 8:18
    版主
  • 方法还是有的,就是效率差点,何解update不能order by?
    下面这句变了两次扫描B表了
    update B set ...=... from (select top 1 * from B b2 where A_Id=1  order by Sort desc )t where B.BID=t.BID

    直接用,不需要再做表連接

    update b2 set ....        from (select top 1 * from B  where A_Id=1  order by Sort desc )b2

    此方法用CTE效率是一樣的,都是內嵌
    ROY WU(吳熹)
    • 已标记为答案 SQL STUDIO 2009年12月1日 9:36
    2009年12月1日 9:16
    版主

全部回复

  • ;with Cte
    as
    (select *,Row=Row_Number()over(Order by Sort desc) from B where A_ID=1)
    
    update Cte
    set .........
    where Row=1
    這樣用,update\Delete用top 1時要小心是采用的是表排序,曾在SQL05環境下測過,剛在SQL08環境測了一下同SQL05一樣。
    結論:樓主最好不要用top 1做為更新判斷
    ROY WU(吳熹 )
    2009年12月1日 7:09
    版主
  • use Tempdb
    go
    create table T(ID int primary  key Clustered,ID2 int,ID3 int)
    
    create index IX_T on T(ID2 asc,ID3 asc)--這里不加上ID3時,表順序會采用聚集索引
    
    insert t(ID,ID2)
    select
        Top 100
        ID=Row_Number()over(order by a.ID),
        ID2=101-Row_Number()over(order by a.ID)
    from syscolumns a,syscolumns b,syscolumns c
    
    
    update top (1) T set ID2=ID2*1000    --更新
    
    select * from T                        --查看結果(ID=100物理順序最后一條/等同ID2=1同表順序的第一條)更新了
    
    delete top (1) T                    --刪除
    
    select * from T                        --查看結果ID2=2表順序的第一條刪除了
    
    
    drop table T
    
    --個人建議:Top做判斷更新和刪除時,不能很好的判斷出表的順序,建議用CTE判斷替換,如表的數據密度、索引、數據量等都是表順序的判斷條件
    貼一下測試的例子給大家參照

    ROY WU(吳熹 )
    • 已标记为答案 SQL STUDIO 2009年12月1日 8:39
    2009年12月1日 8:18
    版主
  • 方法还是有的,就是效率差点,何解update不能order by?
    下面这句变了两次扫描B表了
    update B set ...=... from (select top 1 * from B b2 where A_Id=1  order by Sort desc)t where B.BID=t.BID
    2009年12月1日 8:38
  • 方法还是有的,就是效率差点,何解update不能order by?
    下面这句变了两次扫描B表了
    update B set ...=... from (select top 1 * from B b2 where A_Id=1  order by Sort desc )t where B.BID=t.BID

    直接用,不需要再做表連接

    update b2 set ....        from (select top 1 * from B  where A_Id=1  order by Sort desc )b2

    此方法用CTE效率是一樣的,都是內嵌
    ROY WU(吳熹)
    • 已标记为答案 SQL STUDIO 2009年12月1日 9:36
    2009年12月1日 9:16
    版主
  • 厉害,派生表还可以更新呢,我可真是不知道
    2009年12月1日 9:36