New topic: SQL UPDATE using SELECT
<http://forums.realsoftware.com/viewtopic.php?t=30734> Page 1 of 1 [ 4 posts ] Previous topic | Next topic Author Message RBkanso Post subject: SQL UPDATE using SELECTPosted: Thu Oct 29, 2009 10:56 am Joined: Mon Nov 26, 2007 1:37 pm Posts: 48 I have a set of records that I need to update using a single SQL UPDATE statement, if possible. All I have to do is update a field (Rank) with the order in which it appears in an ORDER BY SQL request (ordered by score). I realize I can get a RecordSet and update the Rank using a while loop but I am wondering if this would be more efficient (or possible) using a single SQL statement. I am thinking something like this: Code:UPDATE Table SET Rank = (SELECT Count(*) FROM Table WHERE Score > (??? Previous score ???) ORDER BY Score ASC) Would also need to take into account "ties" for the same score. So the final output might look something like: Code: Rank Score -------------------- 1 -1 2 0 3 1 3 1 3 1 6 2 6 2 8 5 Top RBkanso Post subject: Re: SQL UPDATE using SELECTPosted: Thu Oct 29, 2009 11:31 am Joined: Mon Nov 26, 2007 1:37 pm Posts: 48 I hate to answer myself instead of just removing the post but I figured someone else may run across this someday... I found what I was looking for from this link: http://www.1keydata.com/sql/sql-rank.html So, taking that example and applying it to my scenario, I think (i.e. untested) the syntax for the SELECT part would be: Code:SELECT COUNT(a2.Score) FROM Table a1, Table a2 WHERE a1.Score > a2.Score or (a1.Score = a2.Score and a1.ID = a2.ID) GROUP BY a1.ID, a1.Score ORDER BY a1.Score ASC, a1.ID ASC And I would simply assign this to the "SET Rank =" part. Top DaveS Post subject: Re: SQL UPDATE using SELECTPosted: Thu Oct 29, 2009 12:37 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 1693 Location: San Diego, CA No need for the ORDER BY statement.... you are only returning ONE value _________________ Dave Sisemore MacPro, OSX 10.5.8 RB2009r2 Top RBkanso Post subject: Re: SQL UPDATE using SELECTPosted: Thu Oct 29, 2009 2:14 pm Joined: Mon Nov 26, 2007 1:37 pm Posts: 48 That brings up another question. Is there a way to SET the Rank for all the records without having to loop through them one-by-one? Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 4 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
