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

Question

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?

Possible duplicate of [Update a table using JOIN in SQL Server?](https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — PM 77-1, Mar 19 '19 at 20:17
Create a SELECT statement that returns both the current value and the value you want returned and then convert that to an UPDATE statement. I found that is the easiest way to do this. — SteveB, Mar 19 '19 at 20:17
@PM77-1 While the similarties are great, my question deals with the same one table whereas the other "seemingly duplicate question" deals with multiple tables. I don't see this as a duplicate. If that is not clear feel free to edit my post. — Pinch, Mar 19 '19 at 20:40

score 2 · Accepted Answer · answered Mar 19 '19 at 20:17

2

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

answered Mar 19 '19 at 20:17

Thom A

88,727
11
45
75

i appreciate your answer! – Pinch Mar 20 '19 at 00:29

score 0 · Answer 2 · answered Jan 21 '22 at 02:24

Came across similar issue. Copying data from one row to another under the same column and inside the same table. Maybe this will help.

   UPDATE MyData 
   SET StreetNumber = newdata.StreetNumber

   FROM (
   SELECT Password FROM [MyDB].[dbo].[MyData]
   WHERE Provider = VersionA
   )newdata

   WHERE Provider = VersionB

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

2 Answers2