Copy data from one row to another in SQL Server in the same table

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?

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