Mysql - INSERT from SELECT with conditional ON DUPLICATE KEY UPDATE -

i'm reading conditional updates on duplicate key based on if statements - e.g., mysql conditional insert on duplicate.

i'm trying similar, within insert select:

insert ignore data1 (id, date, quantity) select id, date, quantity other_table date = '2015-03-01' , id=123 on duplicate key update  quantity = if(quantity null, values(quantity), quantity) 

however, generates error:

#1052 - column 'quantity' in field list ambiguous  

i can't quite figure out how tell mysql 'quantity' field in order resolve ambiguity problem. adding aliases each table doesn't seem (calling data1 'd' throws different error).

anyone have experience this?

you should qualify references quantity field belongs table data1 in on duplicate key update part of query:

insert data1 (id, date, quantity) select id, date, quantity other_table date = '2015-03-01' , id=123 on duplicate key update  quantity = if(data1.quantity null, values(quantity), data1.quantity) 

a shorter way write if() expression use function ifnull() or coalesce():

on duplicate key update  quantity = ifnull(data1.quantity, values(quantity)) 

or

on duplicate key update  quantity = coalesce(data1.quantity, values(quantity)) 

also, there no need use ignore. errors ignore converts warnings not happen more because of on duplicate key update clause.