?
Solved

SQL Server syntax question

Posted on 2008-11-17
6
Medium Priority
?
170 Views
Last Modified: 2012-05-05
I need to update a column depending on the order of another field. For instance, if columA is the lowest I need to update ColumnB to 1. There are multiple records for each account. So accountA can have 5 rows so I need to update columnB in order ascending from columnA...If this doesnt make sense please let me know. Thanks
0
Comment
Question by:healthcheckinc
  • 3
  • 3
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22977418
If you are indeed using SQL Server 2005, you should be able to do this as follows:

;WITH accountOrdered AS (
   SELECT *,
   row_number() OVER (PARTITION BY accountA ORDER BY columnA) AS rNum
   FROM TableName
)
UPDATE accountOrdered
SET columnB = rNum

You can just run the SELECT statement first to see if this is what you meant and then since common table expressions are virtual tables the update should work fine.
0
 

Author Comment

by:healthcheckinc
ID: 22977472
ok I think we are on the same page. I may need a little more clarification though. My table is Transactions and the accountid can have multiple rows and I need to set the position from the revcode ascending. So my query is Select accountid, position, revcode from transactions order by accountid, revcode. Now, I need to update position where the revcode is smallest to greatest. So if I get 3 rows for an accountid I need to update  position for each accountid where the revcodes are in order asc..First recrod would be set position = 1, the next record for the same accountis would be set position = 2 and so on.....Does this make sense?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22977757
Makes perfect sense.  This may work for you as the PARTITION BY means to group on accountid and then ORDER BY revcode so your row_number() will be the order of each revcode ASC for each accountid.

;WITH transOrdered AS (
   SELECT accountid, position, revcode
   , row_number() OVER (PARTITION BY accountid ORDER BY revcode) AS rNum
   FROM Transactions
)
UPDATE transOrdered
SET position = rNum
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:healthcheckinc
ID: 22978049
I dont know who you are but you are my new hero. That was very impressive. Well done...
0
 

Author Closing Comment

by:healthcheckinc
ID: 31517510
awesome
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22978097
Thanks and just glad to help.

Respectfully yours,
Kevin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question