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?

Yawar · Accepted Answer · 2009-04-19 01:29:44Z

2

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

answered Apr 19, 2009 at 1:29

Yawar

11.7k4 gold badges53 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tomas Aschan Over a year ago

Unfortunately I got the following error message when trying to execute the ALTER TRIGGER statement: "The multi-part identifier "bk2_InfoPages.SortOrder" could not be bound."

Sam Saffron · Accepted Answer · 2009-04-19 01:54:18Z

1

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

edited Apr 19, 2009 at 1:54

answered Apr 19, 2009 at 1:47

Sam Saffron

132k81 gold badges336 silver badges511 bronze badges

Comments

Tulip · Accepted Answer · 2023-11-24 23:26:22Z

0

Hope this would work!

UPDATE bk2_InfoPages
SET SortOrder = SortOrder - subquery.c
FROM (
SELECT SortOrder, COUNT(*) AS c
FROM Deleted d
GROUP BY SortOrder
) AS subquery
WHERE subquery.SortOrder <= bk2_InfoPages.SortOrder;

answered Nov 24, 2023 at 23:26

Tulip

213 bronze badges

Collectives™ on Stack Overflow

Using subquerys in Update statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related