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 |
|
stixoffire
Starting Member
17 Posts |
 Posted - 2009-04-28 : 14:53:17
|
| I have 3 tables : tblPack(packID,PackValue) , tblProd(prodID, PackID), tblProdTemp(prodID, PackID) .tblProd - the packID field contains the packValue [This is the problem - it needs to contain packID instead of PackValue) - I want to set the tblProdTemp(PackID)to tblPack(PackID) where tblPack(PackValue) = tblProd(PackID) and tblProdTemp(ProdID) = tblProd(prodID) . I am using the tblProdTemp - to ensure that I get all rows and the correct data first and then it will be simple for me to put the data into tblProd once I have verified it is correct.I can manage to create a select clause to do something like this - but I can not get the update to work. |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-04-28 : 14:59:07
|
| What does your select query look like? If you can get the select to work it shouldn't be too difficult to convert it to an update.Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-04-28 : 15:00:38
|
I am getting dizzy reading your text  Is it possible to describe your problem by providing sample data please?Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
stixoffire
Starting Member
17 Posts |
Posted - 2009-04-29 : 11:20:47
|
| Sure tblPack(packID,PackValue) These values are integers (int) where ID could be 1 to 30, and the PackValue could be any one of the values 1,2,4,6,12,24,36,48,96 etc , tblProd(prodID, PackID), ProdID,PackID are integer ProdId could be 10000, 10001, 11325 ... and PackID IS 1,2,4,6,12,24,36,48,96 etc , tblProdTemp(prodID, PackID) is a duplicate copy of tblProd(prodID, PackID). I want to use the tblPackID as a FK in the tblProd - however those values are currently exact values (equiv to the PackValue) and not the ID value of the tblPack table - which is what I need . I understand dizzy well ..I am there like 3 days ago. |
|
|
|
stixoffire
Starting Member
17 Posts |
Posted - 2009-04-29 : 12:31:48
|
| I created a temporary column in the same table - so it would be easier (I thought) here is what I have any help is appreciated ..The column was just a value field - now it needs to be the ID of the new table which contains those valuesUpdate aSet a.[PackID] = b.[PackID] Select a.[ProductID], a.[PackID], b.[PackID], b.[PackValue]from [Temp_ProdDetail] AS a inner join [CMRC_Pack] AS b on a.[PackID] = b.[PackValue] |
|
|
|
stixoffire
Starting Member
17 Posts |
Posted - 2009-04-29 : 15:45:46
|
| Well your questions certainly helped - caused me to look for a better way . So here is what I did - I created a TempColumn in my Temp table to copy the values from PackID to I then ran this script--Update a--Set a.[PackID] = b.[PackID] --FROM [Temp_Prod] As a, [CMRC_Pack] AS b--Where a.[PackID] = b.[PackValue]This way I ensured my data would not be lost, and could compare the end result for accuracy. One of the issues I was having was the cannot Bind this column etc.. or it would say syntax error near keyword ON - I had the Syntax correct .. well it would work in SQL2K .. |
|
|
|
|
|
|
|
|
Update one table field to a FK field .