CODEWITHSUNDEEP
mysqlsqlsql-updateself-join
alex
alex

Reputation: 63

SQL update null values with another column

I have written this query to update null values from another column which i have extracted using JOIN however, it shows syntex error and not working.

I would like to populate PropertyAddress values from ifnull(a.propertyaddress,b.propertyaddress) for that i have written this query.

enter image description here

select a.ParcelID,a.PropertyAddress, b.ParcelID,b.PropertyAddress,
ifnull(a.PropertyAddress,b.PropertyAddress)
from housingdata a
join housingdata 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 housingdata a  << getting syntex error here*
join housingdata b
on a.ParcelID = b.ParcelID
and a.UniqueID <> b.UniqueID
where a.PropertyAddress is null;

however it shows an error in from clause in update statement.

Upvotes: 1

Views: 1823

Answers (1)

D-Shih
D-Shih

Reputation: 46219

UPDATE ... JOIN syntax in MySQL is different from SQL-Server, you might want to do that, and set PropertyAddress might use right alias because there are two tables holding PropertyAddress from your query.

your logic might set a.PropertyAddress = b.PropertyAddress directly because there is a condition a.PropertyAddress is null.

update housingdata a  
join housingdata b
on a.ParcelID = b.ParcelID
and a.UniqueID <> b.UniqueID 
set a.PropertyAddress = b.PropertyAddress
where a.PropertyAddress is null;

Upvotes: 3

Related Questions