Update column using inner join

  • Hi

    Guys can anybody help me update the rank column as increment of 1

    for every different office for same zipcode, one zipcode has many offices and they need to be ranked as 1,2,3 and can be based on datevalue

    Is it possible to update through innner join

    CREATE TABLE #Table

    (

    ID INT IDENTITY(1,1),

    DateValue DATETIME,

    ZipCode nvarCHAR(5),

    officecode nvarchar(5),

    Rank INT,

    CONSTRAINT PK_YourTable_ID_1 PRIMARY KEY CLUSTERED (ID)

    )

    INSERT INTO #Table

    (DateValue,ZipCode,officecode,rank)

    SELECT '2008-10-17 00:01:00.000','23001','123','1' UNION ALL

    SELECT '2008-10-17 00:02:00.000','23001','133','1' UNION ALL

    SELECT '2008-10-17 00:03:00.000','23001','114','1' UNION ALL

    SELECT '2008-10-17 00:04:00.000','23001','111','1'UNION ALL

    SELECT '2008-10-17 00:05:00.000','23001','112','1'UNION ALL

    SELECT '2008-10-17 00:06:00.000','23002','110','1'UNION ALL

    SELECT '2008-10-17 00:07:00.000','23002','109','1'UNION ALL

    SELECT '2008-10-17 00:08:00.000','23002','108','1'UNION ALL

    SELECT '2008-10-17 00:09:00.000','23003','107','1' UNION ALL

    SELECT '2008-10-17 00:10:00.000','23003','106','1' UNION ALL

    SELECT '2008-10-17 00:11:00.000','23003','105','1' UNION ALL

    SELECT '2008-10-17 00:12:00.000','23003','104','1' UNION ALL

    SELECT '2008-10-17 00:13:00.000','23004','103','1' UNION ALL

    SELECT '2008-10-17 00:14:00.000','23004','102','1' UNION ALL

    SELECT '2008-10-17 00:15:00.000','23004','101','1' UNION ALL

    SELECT '2008-10-17 00:16:00.000','23004','100','1'

    GO

    Thanks

    simon

  • Hello Simon

    If you want to create incremental values in an existing table the fastest (and most simple 😉 ) way would be:

    [font="Courier New"]

    DECLARE @rank INT

    SET @rank = 0

    UPDATE #Table SET @rank = Rank = @rank + 1

    SELECT * FROM #Table

    [/font]

    Greets

    Flo

  • Hi

    Flo

    Thanks for response

    if you read the requirment in detail, you will see its not so simple as incremental increasing the rank

    field. It is increasing the rank to the offices that lies in the same zipcode and so on for another zipcode.

    The expected result will be

    Datevalue Zipcode office rank

    '2008-10-17 00:01:00.000','23001','123','1' UNION ALL

    '2008-10-17 00:02:00.000','23001','133','2' UNION ALL

    '2008-10-17 00:03:00.000','23001','114','3' UNION ALL

    '2008-10-17 00:04:00.000','23001','111','4'UNION ALL

    '2008-10-17 00:05:00.000','23001','112','5'UNION ALL

    '2008-10-17 00:06:00.000','23002','110','1'UNION ALL

    '2008-10-17 00:07:00.000','23002','109','2'UNION ALL

    '2008-10-17 00:08:00.000','23002','108','3'UNION ALL

    '2008-10-17 00:09:00.000','23003','107','1' UNION ALL

    '2008-10-17 00:10:00.000','23003','106','2' UNION ALL

    SELECT '2008-10-17 00:11:00.000','23003','105','3' UNION ALL

  • Try this..

    ;WITH cteTable AS

    (

    SELECT ROW_NUMBER() OVER ( PARTITION BY officecode ORDER BY datevalue ) AS RowNumber, *

    FROM#Table

    )

    UPDATET

    SET Rank = RowNumber

    FROM #Table T

    INNER JOIN cteTable CT

    ON T.ID = CT.ID


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Sorry Try this one ..

    ;WITH cteTable AS

    (

    SELECT ROW_NUMBER() OVER ( PARTITION BY officecode ORDER BY datevalue ) AS RowNumber, *

    FROM #Table

    )

    UPDATE T

    SET Rank = RowNumber

    FROM #Table T

    INNER JOIN cteTable CT

    ON T.ID = CT.ID

    Regards,

    Kingston


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hi

    King

    I tried it but it did not update the rank at all!

    Thanks

    Suman

    Here is the whole thing you can run along with your update statment

    Drop table #Table

    CREATE TABLE #Table

    (

    ID INT IDENTITY(1,1),

    DateValue DATETIME,

    ZipCode nvarCHAR(5),

    officecode nvarchar(5),

    Rank INT,

    CONSTRAINT PK_YourTable_ID_1 PRIMARY KEY CLUSTERED (ID)

    )

    INSERT INTO #Table

    (DateValue,ZipCode,officecode,rank)

    SELECT '2007-10-17 00:01:00.000','23001','123','1' UNION ALL

    SELECT '2007-10-17 00:02:00.000','23001','133','1' UNION ALL

    SELECT '2007-10-17 00:03:00.000','23001','114','1' UNION ALL

    SELECT '2007-10-17 00:04:00.000','23001','111','1'UNION ALL

    SELECT '2007-10-17 00:05:00.000','23001','112','1'UNION ALL

    SELECT '2007-10-17 00:06:00.000','23002','110','1'UNION ALL

    SELECT '2007-10-17 00:07:00.000','23002','109','1'UNION ALL

    SELECT '2007-10-17 00:08:00.000','23002','108','1'UNION ALL

    SELECT '2007-10-17 00:09:00.000','23003','107','1' UNION ALL

    SELECT '2007-10-17 00:10:00.000','23003','106','1' UNION ALL

    SELECT '2007-10-17 00:11:00.000','23003','105','1' UNION ALL

    SELECT '2007-10-17 00:12:00.000','23003','104','1' UNION ALL

    SELECT '2007-10-17 00:13:00.000','23004','103','1' UNION ALL

    SELECT '2007-10-17 00:14:00.000','23004','102','1' UNION ALL

    SELECT '2007-10-17 00:15:00.000','23004','101','1' UNION ALL

    SELECT '2007-10-17 00:16:00.000','23004','100','1'

    GO

    ;WITH cteTable AS

    (

    SELECT ROW_NUMBER() OVER ( PARTITION BY officecode ORDER BY datevalue ) AS RowNumber, *

    FROM #Table

    )

    UPDATE T

    SET Rank = RowNumber

    FROM #Table T

    INNER JOIN cteTable CT

    ON T.Zipcode = CT.Zipcode

    select * from #Table

  • Sorry Try this one ..

    Try this. I hope this works..

    ;WITH cteTable AS

    (

    SELECT ROW_NUMBER() OVER ( PARTITION BY zipcode ORDER BY datevalue ) AS RowNumber, *

    FROM #Table

    )

    UPDATE T

    SET Rank = RowNumber

    FROM #Table T

    INNER JOIN cteTable CT

    ON T.ID = CT.ID

    Regards,

    Kingston


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hi

    King

    Awesome, it worked perfectly as i expected, thanks a lot

    I appreciate all your work and hope to continue this thread if needed:D

    Thanks

    Simon

  • Glad to help you out.

    Regards,

    Kingston


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Waw its working perfactly currect.

    thanx a lot

  • Kingston (3/7/2009)


    Sorry Try this one ..

    Try this. I hope this works..

    ;WITH cteTable AS

    (

    SELECT ROW_NUMBER() OVER ( PARTITION BY zipcode ORDER BY datevalue ) AS RowNumber, *

    FROM #Table

    )

    UPDATE T

    SET Rank = RowNumber

    FROM #Table T

    INNER JOIN cteTable CT

    ON T.ID = CT.ID

    Regards,

    Kingston

    You don't really need to join back CTE table to main table, you can simply update the CTE table to reflect it to the main table. For e.g.

    ;WITH cteTable AS

    (

    SELECT ROW_NUMBER() OVER ( PARTITION BY zipcode ORDER BY datevalue ) AS RowNumber, *

    FROM #Table

    )

    UPDATE cteTable

    SET [Rank] = RowNumber

    --Ramesh


  • Well. I never thought it can be done this way. Thanks for the additional info Ramesh.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • You are welcome!!!

    --Ramesh


Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply