Using composite key to update

Question

Post reply

Using composite key to update

Aspg

Ten Centuries

Points: 1363
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

Aspg Ten Centuries Points: 1363 More actions · Answer 2

Hi ,

here is the attached data.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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

Aspg Ten Centuries Points: 1363 More actions · Answer 4

Sorry ,

I need to update enddate using startdate .

Previousday of startdate should be enddate.

Thanks

David Dubois Right there with Babe Points: 763 More actions · Answer 5

update

[Sample]

set

ImportDate = '2010-08-29'

where

Code = 1

and Rate = 4200

and StarDate = '2004-01-01'

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

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

Aspg Ten Centuries Points: 1363 More actions · Answer 7

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

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

Aspg Ten Centuries Points: 1363 More actions · Answer 9

Aspg

Ten Centuries

Points: 1363

August 11, 2010 at 2:46 am

#1205209

Pls refer the attched sample sheet .

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

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

Aspg Ten Centuries Points: 1363 More actions · Answer 11

Aspg

Ten Centuries

Points: 1363

August 11, 2010 at 3:01 am

#1205217

Order by col1,col2,col3

NO group by

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 12

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

Aspg Ten Centuries Points: 1363 More actions · Answer 13

Aspg

Ten Centuries

Points: 1363

August 11, 2010 at 3:22 am

#1205227

Thanks a lot ...

It worked .

🙂