Selecting and updating data in same procedure

Question

Selecting and updating data in same procedure

rehook2003

SSC-Addicted

Points: 499
More actions
February 19, 2006 at 12:29 pm

#103091

Hi everyone, hope all is well.
I'm having a bit of trouble figuring out how to do the following.
I have a table with the following details
ListingID (int)
Expires (datetime)
DisplayOrder (int)
I'd like to update the DisplayOrder field each day (morning) and have it generate new values randomly from 1 to (however many records exist)
So day 1 might look like this
1000, 1/1/2006, 1
1005, 1/1/2006, 2
1056, 1/1/2006, 3
and the next day would be like this
1005, 1/1/2006, 1
1056, 1/1/2006, 2
1000, 1/1/2006, 3
I know I can select and order by newid() but I'm having trouble looping through the results and updating the new (incremented) value
Any help would be appreciated
Thank you,
Rick

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rehook2003 SSC-Addicted Points: 499 More actions · Answer 1

Hi, it's me again.

I see that a few people have read this post, but I feel that I hadn't explained what I was trying to do as clearly as I'd hoped.

I'm needing to re-order the records on a daily basis using the "DisplayOrder" field.

So each time the procedure is run, the recordset would be populated with a different value in the "DisplayOrder" field, the other data will remain the same.

Any help would be greatly appreciated

Rick

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 2

assuming listingid is the primary key

Step 1:

Create a temporary table with an idenity field (listingid, autoid)

INSERT into mytemptable

SELECT ListingID

from (SELECT ListingID from mytable order by newid)

Step 2:

update mytable

set displayorder=autoid

from mytable inner join mytemptable

on mytable.listingid=mytemptable.listingid

(Step 3: drop mytemptable)

rehook2003 SSC-Addicted Points: 499 More actions · Answer 3

Jo,

Thanks for the help (and quick response), I was able to get started in the right direction.

Although I am having one seemingly small problem.

When I set the ORDER BY NewID() it generates the error below (in query analyzer)

-----------------------------------------------------------

Server: Msg 8101, Level 16, State 1, Line 3

An explicit value for the identity column in table '#tmpFeatured' can only be specified when a column list is used and IDENTITY_INSERT is ON.

-----------------------------------------------------------

CREATE TABLE #tmpFeatured (ListingID int, AutoID int IDENTITY (1,1))

INSERT INTO #tmpFeatured -- (Line 3)

SELECT ListingID from tblListings_Residential_Featured ORDER BY NewID()

UPDATE tblListings_Residential_Featured

SET DisplayOrder = AutoID FROM tblListings_Residential_Featured INNER JOIN #tmpFeatured ON tblListings_Residential_Featured.ListingID = #tmpFeatured.ListingID

SELECT * FROM #tmpFeatured

DROP TABLE #tmpFeatured

SELECT * FROM tblListings_Residential_Featured

------------------------------------------------------------

When I comment out the ORDER BY NewID() on Line 4 it returns results and assigns the AutoID just fine, but the results remain the same due to the missing ORDER BY statement.

I've read about using SET IDENTITY_INSERT #tmpFeatured ON/OFF but no luck adding that, it results in a different error.

Thanks for the help, I really appreciate it.

Rick

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 4

Oops, forgot to specify the field where the values should go into.

INSERT INTO #tmpFeatured (ListingID) -- (Line 3)

SELECT ListingID from tblListings_Residential_Featured ORDER BY NewID()

rehook2003 SSC-Addicted Points: 499 More actions · Answer 5

Worked like a charm!

I really appreciate your help

Thank you,

Rick