Sql-server – Insert into matching on a WHERE clause

sql servert-sqlupdatewhere

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?

Thanks

Best Answer

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

Related Question