CODEWITHSUNDEEP
sql-server
BowerdotJson
BowerdotJson

Reputation: 55

How to update every other row from select query?

I have a SQL query that pulls 160 records from a table in my database.

SELECT TableID, Number, Active FROM Table WHERE Number IN (SELECT Number 
FROM Table
GROUP BY Number, Active
HAVING COUNT(*) > 1 AND COUNT(*) < 3
AND Active = 1)
ORDER BY Number, TableID

What I am trying to do is update the Active flag to 0 in every other row of this query. Something along the lines of:

UPDATE Table SET Active = 0 where Number in (
SELECT TableID, Number, Active FROM Table WHERE Number IN (SELECT Number 
FROM Table
GROUP BY Number, Active
HAVING COUNT(*) > 1 AND COUNT(*) < 3
AND Active = 1 AND Number %2 = 1) -- this is where I need assistance
ORDER BY Number, TableID)

How do I execute this query successfully? The similar questions are not providing the answers I am looking for.

EDIT: Here is the sample shortened data set from the top query:

TableID Number  Active
38      662         1
177     662         1
58      702         1
171     702         1
13      716         1
165     716         1
77      814         1
164     814         1
63      1103        1
174     1103        1

Expected result:

TableID Number  Active
    38      662         1
    177     662         0
    58      702         1
    171     702         0
    13      716         1
    165     716         0
    77      814         1
    164     814         0
    63      1103        1
    174     1103        0

Upvotes: 2

Views: 2977

Answers (1)

Lamak
Lamak

Reputation: 70648

If I understood you correctly, this should do it:

WITH CTE AS
(
    SELECT  *, 
            COUNT(*) OVER(PARTITION BY Number, Active) N,
            ROW_NUMBER() OVER(PARTITION BY Number, Active ORDER BY Number) RN
    FROM dbo.YourTable
    WHERE Active = 1
)
UPDATE CTE
SET Active = 0
WHERE N = 2 AND RN % 2 = 1;

You need to make sure that the query is returning the right rows first though:

WITH CTE AS
(
    SELECT  *, 
            COUNT(*) OVER(PARTITION BY Number, Active) N,
            ROW_NUMBER() OVER(PARTITION BY Number, Active ORDER BY Number) RN
    FROM dbo.YourTable
    WHERE Active = 1
)
SELECT *
FROM CTE
WHERE N = 2;

Upvotes: 5

Related Questions