SQL Server syntax question

Posted on 2008-11-17
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
Question by:healthcheckinc
    LVL 59

    Expert Comment

    by:Kevin Cross
    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.

    Author Comment

    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?
    LVL 59

    Accepted Solution

    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

    Author Comment

    I dont know who you are but you are my new hero. That was very impressive. Well done...

    Author Closing Comment

    LVL 59

    Expert Comment

    by:Kevin Cross
    Thanks and just glad to help.

    Respectfully yours,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now