Using subquerys in Update statement

Question

I have the following SQL statement in a trigger that fires on deletion:

UPDATE bk2_InfoPages
SET SortOrder = SortOrder - (SELECT COUNT(*) FROM Deleted d WHERE d.SortOrder <= SortOrder)

My problem is that the very last SortOrder refers to the Deleted table and not to the bk2_InfoPages table. I am not allowed to add an alias to the bk2_InfoPages table because it's an UPDATE statement - so what should I do instead?

Answer 1

UPDATE bk2_InfoPages
SET SortOrder = SortOrder - (SELECT COUNT(*) FROM Deleted d WHERE d.SortOrder <= bk2_InfoPages.SortOrder)

Answer 2

This should work:

UPDATE b
SET SortOrder = SortOrder - 
  (SELECT COUNT(*) FROM Deleted d WHERE d.SortOrder <= b.SortOrder)
from bk2_InfoPages b

You have to alias your table to do sub queries, for example:

-- this executes fine 
create table #t ( t int)

update t 
set t = (select count(*) from #t t1 where t.t = t1.t)
from #t t