MySQL Forums
Forum List  »  Other Migration

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL UPSERT not recognizing alias
5876
March 24, 2015 05:29AM


Sorry, you can't reply to this topic. It has been closed.

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.