Discuss "Danged Update Query" in the SQL Development forum.

Wolffy · #1 July 17th, 2008, 05:02 PM

OK, so this is one query I can never seem to wrap my head around. I have a table with 3 fields (that are interesting), none unique.
- ProfileID uniqueidentifier
- DateAdded datetime
- PrimaryLvl int

Right now for all records PrimaryLvl is NULL. I need to run an update query to set PrimaryLvl = 1 the earliest record (defined by DateAdded) for each unique ProfileID. I've done this before, but can never seem to quite remember how.

The query I would use to return the records to be updated is:

Code:

Select ProfileId, min(DateAdded)
         From aReallyBigButIndexedTable
         Group By ProfileId

But now how to update?

jmurrayhead · #2 July 17th, 2008, 05:15 PM

I'm not 100% on this, but maybe something like:

Code:

 UPDATE aReallyBigButIndexedTable SET primaryLvl = 1 IN (Select ProfileId, min(DateAdded)
         From aReallyBigButIndexedTable
         Group By ProfileId)

Wolffy · #3 July 17th, 2008, 05:21 PM

Just after I posted, I found the answer. I'll put it here so I know where to look the next time;

Code:

UPDATE   tempInterests
SET      PrimaryLvl = #tmp.One
FROM     tempInterests
JOIN     (
             SELECT   ot.ProfileId, min(ot.DateAdded) [DateAdded], 1 as One
             FROM     tempInterests ot
             GROUP BY ot.ProfileId
         ) #tmp
ON       #tmp.ProfileId = tempInterests.ProfileId
AND         #tmp.DateAdded = tempInterests.DateAdded

There are, no doubt, a few other ways to do it, but this got the job done, and updated about 4.5M records in less the 5 minutes.

(FWIW, I prolly coulda put PrimaryLvl = 1 and it would have worked, but I haven't tried that yet)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
2nd UpdatePanel won't update	Shem	.Net Development	19	July 10th, 2008 09:48 AM
Update Panel	Shem	.Net Development	3	July 10th, 2008 04:37 AM
update gridview dynamically	peebman2000	.Net Development	27	May 8th, 2008 10:03 PM
Update sql connection string in web config	peebman2000	.Net Development	5	April 18th, 2008 12:23 PM

Discuss "Danged Update Query" in the SQL Development forum.

SQL Development - Structured Query Language (SQL) is the talk of databases. Discuss topics such as joins, triggers and other advanced SQL topics.