I have a table that has two variations of data:
Table MYDATA
Address StreetNumber Provider ---------------------------------------------- 123 Main Street 1 VersionA 123 Main Street NULL VersionB
I would like to update Version A’s StreetNumber into Version B.
There will be thousands of records and the match will be on the Address column
I thought to use:
update MYDATA set StreetNumber = (select top 1 streetnumber from MYDATA Goo where Goo.Address = Address and Provider = 'VersionA') where Provider = 'VersionB'
But it seems the nesting is not looking at the row to be updated…rather its picking one record and updating all records with the same streetnumber?
Advertisement
Answer
This should work:
UPDATE MDb SET StreetNumber = MDa.StreetNumber FROM MYDATA MDb JOIN MYDATA MDa ON MDb.[Address] = MDa.[Address] WHERE MDb.Provider = 'VersionB' AND MDa.Provider = 'VersionA'; --You can move this clause to the ON if you prefer