FROM is not valid at this position. UPDATE statement-CodePudding

I have this code from an online SQL Server tutorial. I need to convert it for MySql. I have already changed ISNULL to ifnull, but I still have the error from the question title:

UPDATE a 
SET PropertyAddress = ifnull(a.PropertyAddress, b.PropertyAddress)
FROM Nashvillehousing as a 
JOIN Nashvillehousing as b
    ON a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress IS NULL;

How can I fix this to run on MySql?

CodePudding user response：

You don't need a join for this.

UPDATE nashvillehousing 
SET propertyaddress = 
(
  SELECT ANY_VALUE(src.propertyaddress)
  FROM (select * from nashvillehousing) src
  WHERE src.parcelid = nashvillehousing.parcelid
  AND src.uniqueid <> nashvillehousing.uniqueid
)
WHERE propertyaddress IS NULL;

I had to write FROM (select * from nashvillehousing) src instead of a mere FROM nashvillehousing src. That is due to a limitation in MySQL where you cannot directly select from the same table that you are updating.

The original statement assumes that you find at most two rows for a parcelid. This is usually not guaranteed to be the case, so I put in ANY_VALUE to get this safe. (You can of course just as well use MIN or MAX here, if you like this better.)

CodePudding user response：

The MySql syntax for using a JOIN in an UPDATE is different from SQL Server. I don't write this for MySql very often, but I think you want this:

UPDATE Nashvillehouse as a 
INNER JOIN Nashvillehouse as b 
    ON a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
SET a.PropertyAddress = ifnull(a.PropertyAddress, b.PropertyAddress)
WHERE a.PropertyAddress IS NULL;