June 25, 2009 at 9:17 am
I have a query that is selecting 25 random rows, now, once those rows are selected I want to update a select column in the table so that those records aren't selected again.
Is there a way to accomplish this?
here is my select query: The random records will be passed on the manager that is passed into the query from the web app.
SELECT TOP 25 id, cm, cmnumber FROM feed
where manager= 'smith'
ORDER BY NEWID()
Once the records are selected, I want to update my Selected column so that those records aren't selected again.
June 25, 2009 at 9:39 am
I would suggest you to save the id of the selected rows in a temp table and then update in join with it.
It depends if the update happens in the same session (= without closing the connection). Is it the case?
-- Gianluca Sartori
June 25, 2009 at 9:41 am
It has to be the same and happen in one "swoop"
select the random rows, then mark 'selected' in the table right afterwards.
June 25, 2009 at 9:45 am
Ok, so this could do the trick:
DECLARE @selectedId TABLE (id int)
INSERT INTO @selectedId
SELECT id,
FROM feed
where manager= 'smith'
ORDER BY NEWID()
UPDATE feed
SET selected = 1
FROM feed AS F
INNER JOIN @selectedId AS S
ON F.id = S.id
SELECT F.id, cm, cmnumber
FROM feed AS F
INNER JOIN @selectedId AS S
ON F.id = S.id
-- Gianluca Sartori
June 25, 2009 at 10:02 am
thanks that worked
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply