Post reply

Using composite key to update

  • August 11, 2010 at 1:25 am

    #238832

    Hi ,

    Here is the scenario .

    I want to update a table having 6 columns.

    I want to update a date column .

    I have a composite primary key on col1 , col2 , col3 .

    Now i need to update col5 which is date using this composite primary key .

    How can i achieve this .

    Thanks in Advance

  • August 11, 2010 at 1:46 am

    #1205185

    Please post the table's definition? (Create table)

    What are you updating the column with?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 11, 2010 at 2:06 am

    #1205189

    Hi ,

    here is the attached data.

    I want to use 1st three columns as composite key to update endate column.

  • August 11, 2010 at 2:09 am

    #1205190

    Ok, but what do you want to update the end date with? Where's the new values supposed to come from?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 11, 2010 at 2:18 am

    #1205192

    Sorry ,

    I need to update enddate using startdate .

    Previousday of startdate should be enddate.

    Thanks

  • August 11, 2010 at 2:24 am

    #1205194

    update

    [Sample]

    set

    ImportDate = '2010-08-29'

    where

    Code = 1

    and Rate = 4200

    and StarDate = '2004-01-01'

  • August 11, 2010 at 2:25 am

    #1205195

    So for a row with a startdate of 2004-01-01, you want the end date to be 2003-12-31?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 11, 2010 at 2:37 am

    #1205202

    NO , the row before that should have enddate you specified.

  • August 11, 2010 at 2:41 am

    #1205204

    Before, ordered by what? What and how do you define that one row comes before another?

    For the sample data that you posted, what should the end dates be?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 11, 2010 at 2:46 am

    #1205209

    Pls refer the attched sample sheet .

  • August 11, 2010 at 2:53 am

    #1205213

    You still haven't answered by question on order. 1st, 2nd rows ordered by what? Grouped by what?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 11, 2010 at 3:01 am

    #1205217

    Order by col1,col2,col3

    NO group by

  • August 11, 2010 at 3:10 am

    #1205221

    Something like this ?

    declare @test-2 table

    (code varchar(2) not null,

    Rate varchar(10) not null,

    Stardate datetime Not nUll,

    Enddate datetime Null,

    Importdate datetime null)

    INsert iNTO @test-2 VAlues( 1 ,4200,'2004-01-01',Null,null)

    INsert iNTO @test-2 VAlues( 1 ,4200,'2005-01-01',Null,null)

    INsert iNTO @test-2 VAlues( 2 ,4201,'2004-01-01',Null,null)

    INsert iNTO @test-2 VAlues( 2 ,4201,'2005-01-01',Null,null)

    INsert iNTO @test-2 VAlues( 4 ,4203,'2004-01-01',Null,null)

    INsert iNTO @test-2 VAlues( 4 ,4203,'2005-01-01',Null,null)

    ;WITH TAB As

    (

    SELECT ROW_NUMBER() OVER( PARTITION BY Rate ORDER BY (SELECT code)) RNK,

    code , Rate , Stardate , enddate

    FROM @test-2

    )

    UPDATE T2

    SET T2.enddate = DATEADD (DD, -1, T1.Stardate)

    --SELECT *

    FROMTAB T1

    INNER JOINTAB T2

    ON ( T1.code = T2.code AND (T1.RNK - 1) = T2.RNK )

    SELECT *

    FROM @test-2 T1

  • August 11, 2010 at 3:22 am

    #1205227

    Thanks a lot ...

    It worked .

    🙂

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

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