| Author |
Topic |
|
chrianth
Yak Posting Veteran
50 Posts |
 Posted - 2009-01-06 : 01:47:37
|
| Hi Guys,I want to update the field AssignmentEffectiveDate value that is part of Constraint Type: PRIMARY KEY (clustered)Constraint Keys: FPKey, PGuid, AssignmentEffectiveDate, MarkDeleteIs this possible?Because when I try the query for updateupdate Tableset AssignmentEffectiveDate = '2009-02-29 00:00:00.000'where FKey = 152221I am getting error Msg 2627, Level 14, State 1, Line 1I need this for testing in prod db, as much as posible it would be great if the suggested solution don't require dropping or changing table settings if any. Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 02:47:34
|
| whats the purpose of updating pk? can you explain that? |
 |
|
|
chrianth
Yak Posting Veteran
50 Posts |
Posted - 2009-01-06 : 03:09:47
|
| I want it for testing the report made by our dev here..I want to change the date so that the report would be able to return data, because there is a condition for the output of the report that select certain date of assignment to display. Twicking this Date PK value is the only way I think that I would be able to test the report.My initial solution for this problem would be to insert first the data into a temp table then do the twick, then after that I'll delete the records on the main table then insert back in the values I copied to a temp table together with the twicked data back again to the main table. But, still I'm looking for more better approach than this that can be done by update command only if there would be any. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 03:31:29
|
| is there any other record having same set of values as your updated record? |
|
|
|
chrianth
Yak Posting Veteran
50 Posts |
Posted - 2009-01-06 : 04:34:12
|
| No. I don't think there would be a chance that if I alter the date value of 152221 to 2009-02-09 it would create duplicate entry for the set of PK's. I also verified if indeed would not cause duplicate by checking if there is a record that is existing under the date i want to have on 152221 and my query did not return any.Table Structure:FPKey | PGuid | AssignmentEffectiveDate | MarkDelete | Field_N....etc152221|A1-20-H| 2009-01-09 00:00:00.000 | 0 | etc......152221|A2-21-A| 2009-01-09 00:00:00.000 | 0 | etc......152221|B1-22-B| 2009-01-09 00:00:00.000 | 1 | etc......152222|B8-20-J| 2009-01-09 00:00:00.000 | 1 | etc......152222|B9-80-1| 2009-01-09 00:00:00.000 | 1 | etc......152222|C7-20-5| 2009-01-09 00:00:00.000 | 0 | etc......Thanks for helping out. |
|
|
|
bjoerns
Posting Yak Master
154 Posts |
Posted - 2009-01-06 : 12:07:33
|
| [code]select *into TestTablefrom Table[/code] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 12:21:08
|
quote:
Originally posted by chrianth
No. I don't think there would be a chance that if I alter the date value of 152221 to 2009-02-09 it would create duplicate entry for the set of PK's. I also verified if indeed would not cause duplicate by checking if there is a record that is existing under the date i want to have on 152221 and my query did not return any.Table Structure:FPKey | PGuid | AssignmentEffectiveDate | MarkDelete | Field_N....etc152221|A1-20-H| 2009-01-09 00:00:00.000 | 0 | etc......152221|A2-21-A| 2009-01-09 00:00:00.000 | 0 | etc......152221|B1-22-B| 2009-01-09 00:00:00.000 | 1 | etc......152222|B8-20-J| 2009-01-09 00:00:00.000 | 1 | etc......152222|B9-80-1| 2009-01-09 00:00:00.000 | 1 | etc......152222|C7-20-5| 2009-01-09 00:00:00.000 | 0 | etc......Thanks for helping out.
is there any trigger existing on your table? |
|
|
|
chrianth
Yak Posting Veteran
50 Posts |
Posted - 2009-01-07 : 22:10:49
|
quote:
Originally posted by visakh16
quote:
Originally posted by chrianth
No. I don't think there would be a chance that if I alter the date value of 152221 to 2009-02-09 it would create duplicate entry for the set of PK's. I also verified if indeed would not cause duplicate by checking if there is a record that is existing under the date i want to have on 152221 and my query did not return any.Table Structure:FPKey | PGuid | AssignmentEffectiveDate | MarkDelete | Field_N....etc152221|A1-20-H| 2009-01-09 00:00:00.000 | 0 | etc......152221|A2-21-A| 2009-01-09 00:00:00.000 | 0 | etc......152221|B1-22-B| 2009-01-09 00:00:00.000 | 1 | etc......152222|B8-20-J| 2009-01-09 00:00:00.000 | 1 | etc......152222|B9-80-1| 2009-01-09 00:00:00.000 | 1 | etc......152222|C7-20-5| 2009-01-09 00:00:00.000 | 0 | etc......Thanks for helping out.
is there any trigger existing on your table?
How do I check for triggers? |
|
|
|
chrianth
Yak Posting Veteran
50 Posts |
Posted - 2009-01-07 : 22:58:13
|
quote:
Originally posted by chrianth
quote:
Originally posted by visakh16
quote:
Originally posted by chrianth
No. I don't think there would be a chance that if I alter the date value of 152221 to 2009-02-09 it would create duplicate entry for the set of PK's. I also verified if indeed would not cause duplicate by checking if there is a record that is existing under the date i want to have on 152221 and my query did not return any.Table Structure:FPKey | PGuid | AssignmentEffectiveDate | MarkDelete | Field_N....etc152221|A1-20-H| 2009-01-09 00:00:00.000 | 0 | etc......152221|A2-21-A| 2009-01-09 00:00:00.000 | 0 | etc......152221|B1-22-B| 2009-01-09 00:00:00.000 | 1 | etc......152222|B8-20-J| 2009-01-09 00:00:00.000 | 1 | etc......152222|B9-80-1| 2009-01-09 00:00:00.000 | 1 | etc......152222|C7-20-5| 2009-01-09 00:00:00.000 | 0 | etc......Thanks for helping out.
is there any trigger existing on your table?
How do I check for triggers?
I found it...there was no trigger on my table. |
|
|
|
chrianth
Yak Posting Veteran
50 Posts |
Posted - 2009-01-11 : 23:45:20
|
| hi guys, any other suggestion? or there are really no way I can update a PK value. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-12 : 02:22:07
|
| the error message seems like its causing pk violation. so i'm tempted to belive the update is infact creating duplicate set in table |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-12 : 03:27:37
|
[code]DECLARE @Sample TABLE ( i INT, j INT, PRIMARY KEY CLUSTERED ( i, j ) )INSERT @SampleSELECT 1, 1 UNION ALLSELECT 2, 2SELECT *FROM @Sample-- Update is OK as long as the primary key doesn't conflictUPDATE @SampleSET i = 3, j = 3WHERE i = 2SELECT *FROM @Sample-- Throw same error as original posterUPDATE @SampleSET i = 3, j = 3WHERE i = 1[/code]
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
Update on PK value, is it posible?