CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ken Carter
Ken Carter

Reputation: 355

ROW_NUMBER() OVER with sub set

I've been leveraging information gleaned from other thread and what not and have gotten really close but am missing something here to do what I need to do. Here is my code that as I have it up right now in a SQL query window:

WITH n AS (
    SELECT  sub_idx AS current_id,
    ROW_NUMBER() OVER (PARTITION BY EID ORDER BY alt_sub_idx) AS new_id
    FROM
        GETT_Documents 
        )
        UPDATE GETT_Documents
        SET sub_idx = n.new_id
    FROM n
    WHERE EID = 'AC-1.1.i';

This seemed like it should work but instead of numbering the sub_idx column from 1 to 11 it put all 1's in that column.

View of relevant rows

Can someone with sharp eyes point out the error of my ways first off? Then perhaps suggest how I might change this to increment by 10's instead of single digits because I would like to turn around and do the same thing to the alt_sub_idx column after doing this to to this column but in increments of 10.

Regards, Ken...

Upvotes: 1

Views: 103

Answers (6)

S.A
S.A

Reputation: 196

Two options:

  1. Either use an attribute at a higher level than existing column in the Partition By clause

OR

  1. As suggested by Akshey in the previous answer, remove the Partition By altogether.

Upvotes: 0

Ken Carter
Ken Carter

Reputation: 355

P Salmon provided this code which I adopted and the only reason for me posting this answer here is so as to show what I am going to be placing in the stored procedure as a final result. This is working perfectly and enumerating both the sub_idx and alt_sub_idx columns as it should. Below is the actual code I will be using and the results to compare with what was posted in my original post.

Thanks to P Salmon as well as everyone else who contributed here I learned a great deal from everyone!

WITH n AS 
(
SELECT  DID AS DID,
        sub_idx AS current_id,
        ROW_NUMBER() OVER (PARTITION BY EID ORDER BY alt_sub_idx) AS new_id
FROM    GETT_Documents 
)
--SELECT * FROM N
UPDATE  GETT_Documents
        SET sub_idx = n.new_id
FROM    GETT_Documents  G
JOIN    n ON N.DID = G.DID
WHERE   EID = 'AC-1.1.i';

WITH n AS ( SELECT DID AS DID, alt_sub_idx AS current_id, ROW_NUMBER() OVER (PARTITION BY EID ORDER BY sub_idx)*10 AS new_id FROM GETT_Documents ) --SELECT * FROM N UPDATE GETT_Documents SET alt_sub_idx = n.new_id FROM GETT_Documents G JOIN n ON N.DID = G.DID WHERE EID = 'AC-1.1.i';

the output

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17640

DECLARE @GETT_DOCUMENTS TABLE
(DID INT, EID VARCHAR(1), SUB_IDX INT, ALT_SUB_IDX INT)
INSERT INTO @GETT_DOCUMENTS
VALUES
(1,'A',0,10),
(2,'A',0,20),
(3,'A',0,30),
(4,'A',0,40),
(5,'A',0,50),
(6,'A',0,60),
(7,'A',0,70),
(8,'A',0,80),
(9,'A',0,90),
(10,'A',0,100),
(11,'A',0,110),
(12,'A',0,120)

;WITH n AS 
    (
    SELECT  DID AS DID,
            sub_idx AS current_id,
            ROW_NUMBER() OVER (PARTITION BY EID ORDER BY alt_sub_idx) AS new_id
    FROM    @GETT_Documents 
    )
    --SELECT * FROM N
    UPDATE  @GETT_Documents
            SET sub_idx = n.new_id
    FROM    @GETT_Documents  G
    JOIN    n ON N.DID = G.DID
    WHERE   EID = 'A';

SELECT * FROM @GETT_Documents

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

SQL Server supports updatable CTES, so you don't need the JOIN:

WITH toupdate AS (
      SELECT sub_idx AS current_id,
             ROW_NUMBER() OVER (PARTITION BY EID ORDER BY alt_sub_idx) AS new_id
      FROM GETT_Documents 
     )
UPDATE toupdate
    SET sub_idx = new_id
    WHERE EID = 'AC-1.1.i';

The problem with your query is the lack of join condition. It does a cross join, so there is no saying what row is used for the update. However, the query doesn't need a join at all.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Your UPDATE isn't correlated, so it is just grabbing the first row from the cte everytime. It needs to be like this:

...
UPDATE d        
SET sub_idx = n.new_id
FROM n
INNER JOIN GETT_Documents d
  ON d.sub_idx=n.sub_idx
WHERE d.EID = 'AC-1.1.i';

Upvotes: 1

Akshey Bhat
Akshey Bhat

Reputation: 8545

Remove partition by clause. Eid might be unique that's why you are getting 1's in output.

Upvotes: 0

Related Questions