February 26, 2004 at 9:39 am
I was hoping that someone could help me figure out the appropriate query for the following situation. I have two tables, in two different datbases. For each row in table A, I need to replace a value in table A with the a value from a corresponding row in table B. There is a one-to-one key mapping between both tables. In other words they look something like this:
Table A:
RecId (pk)
valueToBeReplaced
Table B:
RecId (fk)
someValue.
There are 845 records in each table. My sense is that I will need a while statement that loops through each row in B, retrieves the the value and updates the correspoding row in Table A with that value. Problem is I do not know who to code that in SQL.
Any help would be greatly appreciated.
Thanks,
Chad
February 26, 2004 at 10:24 am
I am assuming from your post TableB RecID is primary Key on that table
UPDATE TableA Set ValueToBeReplaced = B.SomeValue
From TableB B
Where TableA.RecId = B.RecID
If you are possitive that there are the same number of records and the same primary kes That's all you need, but if you need to add to A what is in B and not yet in A you will need:
Insert TableA (fld1,fld2..)
SELECT B.fld1,B.fld2,...
FROM TableB B left outer join TableA A on A.RecId = B.RecId
Where A.RecId is Null
HTH
* Noel
February 28, 2004 at 1:53 am
When the two tables are residing on two different databases is it not a must to prefix the database name to the table name like
UPDATE database1.dbo.TableA Set ValueToBeReplaced = B.SomeValue
From database2.dbo.TableB B
Where database1.dbo.TableA.RecId = B.RecID
Prasad Bhogadi
www.inforaise.com
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply