rwheeler23
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rwheeler23, do you still need help with this question?
ASKER
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.