Skip to content
Advertisement

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?

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
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement