I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (all Number values are unique).

I tried this but I'm getting invalid column name

INSERT INTO dbo.B ( [ID] ) 
SELECT orig_id
FROM dbo.A a, dbo.B b
WHERE b.[NUMBER] = a.number

but a whole bunch of new rows are created instead of updating the existing ones. How can I update the ID column of table B with the id's from table A matching on the Number values?


update dbo.B 
set b.[ID] = a.orig_id
FROM dbo.A a
join dbo.B b
on b.[NUMBER] = a.number 
and b.[ID] <> a.orig_id

the <> is so it can avoid taking a lock if the are equal

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.