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
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 =''
How do I add the correct PropertyAddress to the
With your code in the
UPDATE statement you are actually joining twice the table
Also you should check if the column
PropertyAddress of the updated copy of
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, '') <> '';