SQL update null values with another column

Question

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.

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.

In a select statement, you need to specify what and then which table from, but in an update statement you specify which table is updated first and then what to set in it. The join doesn't really differ, though as pointed out, you might want the join to sit with the main table. where clauses are also identical between them. — SEoF
– SEoF, Commented Feb 16, 2022 at 13:37

D-Shih · Accepted Answer · 2022-02-16 13:42:46Z

3

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;

edited Feb 16, 2022 at 13:42

answered Feb 16, 2022 at 13:34

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

alex Over a year ago

got error column 'PropertyAddress' in field list is an ambigious' @D-Shih

D-Shih Over a year ago

I edit my answer and add some suggestions.

alex Over a year ago

perfect, worked:) Many thanks.

D-Shih Over a year ago

@alex No problem glad to help

Collectives™ on Stack Overflow

SQL update null values with another column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related