Insert into (with select statement) and additional ON DUPLICATE KEY UPDATE based on the select statement

Question

I have a quite nested mysql query and would like to insert or update values based an on a select statement.

The following code is simplified however demonstrates the problem I have.

INSERT INTO product_feeds(`product_id`,`vat_dk`) select p.product_id,
round(((select p2.`calc_value` from product_prices p1 left join calcs p2 on   
p1.product_tax_id=p2.calc_id where p1.product_id=p.product_id and p1.product_currency = '40')/100+1),2) 
as vat_dk from products p where vendor_id = 1 ON DUPLICATE KEY UPDATE `vat_dk` = '1.25'

The value from the INSERT query is set correct. However how can I set the correct value also for the ON DUPLICATE KEY UPDATE part. Whenever I replace the "1.25" with the above query it throws me errors.

Any help is very appreciated.

Answer 1

You can use VALUES() to refer the a value of that would have been otherwise inserted.

So:

insert into product_feeds(`product_id`,`vat_dk`) 
select 
    p.product_id,
    round(
        (
            (
                select p2.`calc_value` 
                from product_prices p1 
                left join calcs p2 on p1.product_tax_id = p2.calc_id 
                where p1.product_id = p.product_id and p1.product_currency = '40'
            ) / 100 + 1
        ), 2
    ) as vat_dk 
from products p 
where vendor_id = 1 
on duplicate key update vat_dk = values(vat_dk)