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)
A.user_id, 'num_products_observed',
when A.purchased is null then 0
else A.purchased/
end 'num_purchased_percent'
(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
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 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

Written By
MySQL UPSERT not recognizing alias
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.