SQL Syntax for Update by Ranking

What is the proper syntax to turn this into an UPDATE statement?
I need to set the value of INACTIVE to 1 for all records where the value of VersionRank is greater than one.
I have tried several variations and it keeps setting all the values to 1.

SELECT
      *
FROM (
      SELECT
            QUOTE_NUMBER_OEQH
          , SEQUENCE_NUMBER_OEQH
          , DATE_OF_QUOTE_OEQH
          , TOTAL_SELL_PRC_OEQH
          , INACTIVE
          , ROW_NUMBER() OVER (PARTITION BY QUOTE_NUMBER_OEQH
                              ORDER BY SEQUENCE_NUMBER_OEQH DESC) AS VersionRank
      FROM CSTQUTHD
    ) AS derived
ORDER BY
      QUOTE_NUMBER_OEQH
    , SEQUENCE_NUMBER_OEQH
    , VersionRank
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
assuming the row identifier is given by the first two columns under your ORDER BY... clause you can do this otherwise adjust the matching columns for the correct update:

--if you have millions rows to update in the #list table I suggest add and index on the two columns matching the record back to the parent table

SELECT
      * into #list
FROM (
      SELECT
            QUOTE_NUMBER_OEQH
          , SEQUENCE_NUMBER_OEQH
          , DATE_OF_QUOTE_OEQH
          , TOTAL_SELL_PRC_OEQH
          , INACTIVE
          , ROW_NUMBER() OVER (PARTITION BY QUOTE_NUMBER_OEQH
                              ORDER BY SEQUENCE_NUMBER_OEQH DESC) AS VersionRank
      FROM CSTQUTHD
    ) AS derived
where VersionRank > 1
ORDER BY
      QUOTE_NUMBER_OEQH
    , SEQUENCE_NUMBER_OEQH
    , VersionRank;



UPDATE CSTQUTHD WITH (ROWLOCK) SET INACTIVE = 1
from #list
where CSTQUTHD.QUOTE_NUMBER_OEQH=#list.QUOTE_NUMBER_OEQH and CSTQUTHD.SEQUENCE_NUMBER_OEQH=#list.SEQUENCE_NUMBER_OEQH;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
WITH cte_versionrank AS (
      SELECT
            QUOTE_NUMBER_OEQH
          , SEQUENCE_NUMBER_OEQH
          , DATE_OF_QUOTE_OEQH
          , TOTAL_SELL_PRC_OEQH
          , INACTIVE
          , ROW_NUMBER() OVER (PARTITION BY QUOTE_NUMBER_OEQH
                              ORDER BY SEQUENCE_NUMBER_OEQH DESC) AS VersionRank
      FROM CSTQUTHD
)
UPDATE cv
SET INACTIVE = 1
FROM cte_versionrank cv
WHERE cv.VersionRank > 1
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
rwheeler23, do you still need help with this question?
0
rwheeler23Author Commented:
My apologies for the reply. There are about 1.5 millions records that need to be updated.  Fortunately this is a one shot update so either solution would work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.