Using a self join to update NULL values in SQLite-CodePudding

So I have a table with propertyaddress values missing, although the ParcelID column can be used to identify the value. How do I perform a self-join in sqlite to update the NULL values with their true propoertyaddress values?

I tried the following code

SELECT a.ParcelID, a.PropertyAddress, b.ParcelID, b.PropertyAddress, IFNULL(a.PropertyAddress, b.PropertyAddress)
FROM housing_data a
JOIN housing_data b
    ON a.ParcelID = b.ParcelID
    AND a.[UniqueID] <> b.[UniqueID]
WHERE a.PropertyAddress is null;


UPDATE a
SET PropertyAddress = IFNULL(a.PropertyAddress, b.PropertyAddress)
FROM housing_data  a
WHERE a.PropertyAddress IS NULL;
JOIN housing_data  b
    ON a.ParcelID = b.ParcelID
    AND a.[UniqueID] <> b.[UniqueID]

This code throws an errors

CodePudding user response：

If your version of SQLite is 3.33.0 you can use the UPDATE FROM syntax:

UPDATE housing_data AS d1
SET PropertyAddress = d2.PropertyAddress
FROM housing_data AS d2
WHERE d2.ParcelID = d1.ParcelID AND d1.PropertyAddress IS NULL AND d2.PropertyAddress IS NOT NULL;