Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Denizen
Starting Member
7 Posts |
 Posted - 2009-12-28 : 17:35:00
|
| In a MEMBERS table, I have a UniqueID called "ID." Although the numbers are unique, they are not autoincrementing (they come to me from a separate source). I have created an autoincrementing field called AB_ID in the MEMBERS table and have populated it. I have also created the same (currently NULL valued) AB_ID field in a table called MEMBERHISTORY.What I'd like to be able to do is UPDATE the AB_ID field in MEMBERHISTORY based on the values in the MEMBERS table where ID is the primary key. The MEMBERHISTORY table uses ID as a foreign key.I know this is an update query, but my T-SQL syntax isn't up to par. Any assistance? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-12-28 : 18:43:20
|
| If I'm reading you correctly, and I won't be surprised if I'm not, you want to update the AB_ID column in the history table based on the values in the ID column. My first question becomes, why do you want a duplicate primary key? My second question becomes, what is wrong with a non-autoincrementing key?In any event, does this address your requirements?update MemberHistoryset AB_ID = m.AB_IDfrom Member minner join MemberHistory mhon m.ID = mh.ID=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-29 : 01:09:07
|
quote:
Originally posted by Bustaz Kool
If I'm reading you correctly, and I won't be surprised if I'm not, you want to update the AB_ID column in the history table based on the values in the ID column. My first question becomes, why do you want a duplicate primary key? My second question becomes, what is wrong with a non-autoincrementing key?In any event, does this address your requirements?update mhset AB_ID = m.AB_IDfrom Member minner join MemberHistory mhon m.ID = mh.ID=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain)
MadhivananFailing to plan is Planning to fail |
|
|
|
Denizen
Starting Member
7 Posts |
Posted - 2009-12-29 : 21:11:38
|
| Ultimately I want to change the Primary key from ID to A_ID. I won't have two Primary keys.The reason is that A_ID has more "members" than the ID field (which comes from a different source).I believe that the code provided will do the trick. Thank you in advance . . . and Happy New Year! |
|
|
|
|
|
|
|
|
Different Update Query