UPDATE NULL of a column matching the ID in the same table

Question

I'm working on housing data with several columns. In the cleaning process, I noticed that in the column 'PropertyAddress' are a few Null, and I want to confirm if those Null could be matched with the ParcelID. So, I wrote the following query to confirm the previous affirmation with the result shown below the query.

SELECT a.ParcelID, a.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null;

ParcelID	PropertyAddress	ParcelID	PropertyAddress
092 13 0 322.00	NULL	092 13 0 322.00	237 37TH AVE N, NASHVILLE
043 04 0 014.00	NULL	043 04 0 014.00	112 HILLER DR, OLD HICKORY
026 05 0 017.00	NULL	026 05 0 017.00	208 EAST AVE, GOODLETTSVILLE
042 13 0 075.00	NULL	042 13 0 075.00	222 FOXBORO DR, MADISON

After confirming that I could use ParcelID to change the Nulls with the correct PropertyAddress, I wrote the UPDATE query:

UPDATE nashville_housing
SET PropertyAddress = ( 
SELECT a.ParcelID, b.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null);

and give the error 'Error Code: 1241. Operand should contain 1 column(s)'

So, I rewrite the query to:

UPDATE a
SET PropertyAddress = IFNULL(a.PropertyAddress,b.PropertyAddress)
WHERE a.PropertyAddress is null;

and give the error 'Error Code: 1146. Table 'nasville_housing.a' doesn't exist'

Finally, I wrote:

UPDATE a
SET PropertyAddress = IFNULL(a.PropertyAddress,b.PropertyAddress) in (
SELECT a.ParcelID, b.PropertyAddress, b .ParcelID, b.PropertyAddress
FROM nashville_housing a
JOIN nashville_housing b
    on a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress is null);

but give the error 'Error Code: 1146. Table 'nasville_housing.a' doesn't exist'

I appreciate the support anyone can give me.

Answer 1

You can UPDATE using JOIN: https://www.mysqltutorial.org/mysql-update-join/

UPDATE nashville_housing a
LEFT JOIN nashville_housing b ON a.ParcelID = b.ParcelID 
AND a.UniqueID <> b.UniqueID 
SET a.PropertyAddress = b.PropertyAddress 
WHERE
    a.PropertyAddress IS NULL;