Search code examples
sqlsqlitejoinsql-updatesql-null

How to copy data from one column to another?

I am trying to add Property Address in columns that have a missing value.

I use the below to identify common parcel IDs with corresponding property address since the same parcelIDs have the same PropertyAddress as well.

select n.UniqueID, n.ParcelID, n.PropertyAddress, n2.UniqueID, n2.ParcelID, n2.PropertyAddress, IFNULL(n.PropertyAddress,n2.PropertyAddress) 
from Nashnew n 
join Nashnew n2 
on n.ParcelID = n2.ParcelID 
where n2.PropertyAddress =''
and n.UniqueID != n2.UniqueID

I get this result: enter image description here

Now I want to add the data in column IFNULL(n.PropertyAddress,n2.PropertyAddress) to the missing PropertyAddress cells using the below:

UPDATE Nashnew 
set propertyAddress = IFNULL(n.PropertyAddress,n2.PropertyAddress)
from Nashnew n 
join Nashnew n2 
on n.ParcelID = n2.ParcelID 
and n.UniqueID != n2.UniqueID 
where n2.PropertyAddress =''

However, I get this result, where all the PropertyAddress are the same for all rows. enter image description here

How do I add the correct PropertyAddress to the

Solution

  • With your code in the UPDATE statement you are actually joining twice the table Nashnew.
    Also you should check if the column PropertyAddress of the updated copy of Nashnew is null or empty:

    Change to this:

    UPDATE Nashnew AS n
SET propertyAddress = n2.PropertyAddress
FROM Nashnew AS n2 
WHERE n.ParcelID = n2.ParcelID 
  AND n.UniqueID <> n2.UniqueID 
  AND COALESCE(n.PropertyAddress, '') = ''
  AND COALESCE(n2.PropertyAddress, '') <> '';