Search code examples
sqlsqlitejoinsql-updatesyntax-error

Update issue using join in SQLite

Good morning all,

Apologies if this has been addressed, I am attempting to run the following query. I have duplicated my table in order to create a join.

I have identified the null Propertyaddress in table A (28 cells had missing Property Addresses in table A). I then hope to insert addresses from PropertyAddress table B by joining table A to table B.

Although the query joining table A to table B runs correctly, I am unable to update the table due to an operational error.

I am able to successfully run:

%%sql
SELECT A1.PropertyAddress, A1.ParcelID, B2.PropertyAddress, B2.ParcelID, IFNULL(A1.PropertyAddress, B2.PropertyAddress)
FROM Nashvillehousing A1
JOIN Nashvillehousing B2
ON A1.ParcelID = B2.ParcelID
AND A1.UniqueID <> B2.UniqueID
WHERE A1.PropertyAddress IS NULL

However, I can not run the following:

(sqlite3.OperationalError) no such table: A1
[SQL: UPDATE A1
SET PropertyAddress = IFNULL(A1.PropertyAddress, B2.PropertyAddress)
FROM Nashvillehousing as A1
JOIN Nashvillehousing as B2
ON A1.ParcelID = B2.ParcelID
AND A1.UniqueID <> B2.UniqueID
WHERE A1.PropertyAddress IS NULL]

I appreciate any input!

I have tried different aliases, periods, underscores however my aliased tables are still not recognised for an UPDATE query.

I expect the missed values to be inserted into table A1 from table B2 as an updated table with no missing property addresses.

Solution

  • You are using SQL Server's syntax for a join in the UPDATE statement.

    The correct syntax for SQLite is:

    UPDATE Nashvillehousing AS A1
SET PropertyAddress = B2.PropertyAddress
FROM Nashvillehousing AS B2
WHERE A1.ParcelID = B2.ParcelID AND A1.UniqueID <> B2.UniqueID
  AND A1.PropertyAddress IS NULL AND B2.PropertyAddress IS NOT NULL;

    Also, I changed IFNULL(A1.PropertyAddress, B2.PropertyAddress) to just B2.PropertyAddress because the WHERE clause returns only rows with null in PropertyAddress.