MySQL UPSERT not recognizing alias
Posted by:
Vips Ch
Date: March 24, 2015 05:29AM
have a query that performs an UPSERT (Insert - but if exists, update).
MySQL complains it isn't valid, here is the query:
insert into
mytable (user_id, num_products_observed, num_purchased_percent)
(select
A.user_id,
B.total 'num_products_observed',
case
when A.purchased is null then 0
else A.purchased/B.total
end 'num_purchased_percent'
from
(select user_id, count(prod_observed) 'total' from products where user_id = ? ) B
left join (select user_id, count(prod_purch) 'purchased' from products_purchased) A on B.user_id = A.user_id
) newsum -- <--- ISSUE IS HERE
ON DUPLICATE KEY UPDATE
num_products_observed = newsum.num_products_observed,
num_purchased_percent = newsum.num_purchased_percent
I hope this makes sense to you. The issue is at the line which reads ) newsum. MySql complains about the alias I'm giving the table. user_id is unique in this table (mytable).
It IS possible that B.total is null, in which case everything in newsum is null - which is fine, then I don't want to insert or update anything (or an update with user_id and zeros for all would be fine too).
Any thoughts on what I'm doing wrong? Thanks in advance
Subject
Views
Written By
Posted
MySQL UPSERT not recognizing alias
6201
March 24, 2015 05:29AM
Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.