Schedule Stored Procedure: Changing one Field based on another

Hi all,

I want to do something that I can't get my head around. Here is the info..

I have a table, Tips. This is a table holding playing tips for a department of music web site. The tips are broken down into areas (brass, woodwind, etc...). Each area has its own .aspx page which has a user control that will display a "tip of the week." That tip will be taken from the table. Columns are:

ID, int, pk
AreaID, int
CategoryID, int
Active, bit
Selected, bit
Author, nvarchar
TipText, text

For what I want to do the only fields that are in play are the ID, AreaID and Selected. The table will have multiple rows for each AreaID, and at any one given time only one of the multiple rows with the same AreaID will be selected (Selected = 1). The others will be 0.

I'm trying to figure out how to write a stored procedure that, scheduled to run weekly, will select one random tip for each disctinct area id (setting selected = 1) that is not currently selected and then deselecting the previously selected items (setting selected = 0).

Doing this for a single record in the table would be easy. The tricky part for me is doing this for each of the distinct AreaIDs.

Ideas?

RichardWow. No replies.

Did I not ask the question right? I'm still trying to figure this out. If it's a stupid question I'd appreciate a heads up about where I should look to find this info.

Thanks!

Richard|||Have you had any luck? This is actually trickier than it sounds and I am not coming up with anything clever. I think you are going to have to resort to using a cursor -- the cursor will contain a unique set of AreaIDs, and you will loop through it.

What I was able to come up with was this (note I didn't put the cursor creation and looping code in there because I don't know the syntax off of the top of my head -- BOL is our friend):

-- temporarily make the currently selected items = 2 to keep them out of consideration
UPDATE
 tips
SET
 Selected = 2
WHERE
 Selected = 1
-- create a cursor consisting of the unique AreaIDs
-- then loop through cursor, updating Selected for each AreaID
UPDATE
 tips
SET
 Selected = 1
FROM
 tips
INNER JOIN
(SELECT TOP 1 ID FROM tips WHERE areaID = @.cursor_AreaID AND Selected <> 2 ORDER BY NEWID()) AS SubSelect ON Tips.ID = SubSelect.ID
WHERE
 Selected <> 2 AND
 AreaID = @.cursor_AreaID
-- when done looping, make the originally selected IDs unselected
UPDATE
 tips
SET
 Selected = 0
WHERE
 Selected = 2

I am hopeful this could be done without a cursor but it's not coming to me...

Terri|||Terri,

Clever! I didn't think about the setting the 0's to 2 part. I don't know what a cursor is, but I'll poke around and figure it out - give me a day or so. :) The rest make perfect sense, and should help me get it done. Thanks for your help!

Richard|||Since I now know you are still working on this, I still stand by my suggestion, but I can give you code to use instead of using a cursor (which is resource intensive). Same approach, but you would put all of your AreaIDs into a #temp table instead and loop though that:

DECLARE @.areaID int
-- temporarily make the currently selected items = 2 to keep them out of consideration
UPDATE
 tips
SET
 Selected = 2
WHERE
 Selected = 1
-- create a temp table consisting of the unique AreaIDs
CREATE TABLE #Temp (areaID int)
INSERT INTO #Temp SELECT DISTINCT areaID FROM tips
-- get first areaID to process
SELECT TOP 1 @.areaID = areaID FROM #temp ORDER BY areaID
-- loop
WHILE @.areaID IS NOT NULL
BEGIN
-- update Selected for each AreaID
UPDATE
 tips
SET
 Selected = 1
FROM
 tips
INNER JOIN
(SELECT TOP 1 ID FROM tips WHERE areaID = @.areaID AND Selected <> 2 ORDER BY NEWID()) AS SubSelect ON Tips.ID = SubSelect.ID
WHERE
 Selected <> 2 AND
 AreaID = @.AreaID
-- get next AreaID to process
DELETE FROM #temp WHERE areaID = @.areaID
SELECT @.areaID = NULL
SELECT TOP 1 @.areaID = areaID FROM #temp ORDER BY areaID
END
-- when done looping, make the originally selected IDs unselected
UPDATE
 tips
SET
 Selected = 0
WHERE
 Selected = 2
-- drop temporary table
DROP TABLE #TEMP

Still wishing this could be done in one statement...

Terri|||Terri,

Worked perfectly! All I had to do is change the column type from bit to int (couldn't update all the 1's to 2 in a bit field). Thank you!!

RH|||That's good news; thanks for updating us! (And yet another reason why I don't like bit fields ;-)) You should be able to use a tinyint here and save some storage space.

Terro