Set the value of the column matching ID in the same table to NULL

Question

I'm working with housing data with multiple columns. During the cleaning process, I noticed that there are some Null values ​​in the "PropertyAddress" column, and I wanted to confirm if these Null values ​​can be matched with the ParcelID. Therefore, I wrote the following query to confirm the previous assertion and display the results 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;

Package ID	Attribute address	Package ID	Attribute address
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 Hwalnut
026 05 0 017.00	null	026 05 0 017.00	208 East Avenue, Goodlettville
042 13 0 075.00	null	042 13 0 075.00	222 FOXBORO DR, Madison

After confirming that I can use ParcelID to change the Null value to 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);

But an error occurred "Error Code: 1241. Operand should contain 1 column(s)"

So, I rewrote the query as:

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

But 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 the error "Error Code: 1146. Table 'nasville_housing.a' doesn't exist"

I appreciate any support anyone can give me.

Answer 1

You can use JOIN for UPDATE operations: 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;