Use results returned from select subquery in update query

Question

I am having a query in which i need to update a table. This is my select query:

SELECT  so.fk_customer,
IF (((sum( lgg.fk_catalog_attribute_option_global_gender = 1 )/count( lgg.fk_catalog_attribute_option_global_gender )) *100 > 60) AND (c.gender='male'), 'Men',
IF (((sum( lgg.fk_catalog_attribute_option_global_gender = 2 )/count( lgg.fk_catalog_attribute_option_global_gender )) *100 > 60) AND (c.gender='female'), 'Women', '') ) as calculatedGender
FROM catalog_attribute_link_global_gender AS lgg
INNER JOIN catalog_simple AS cs ON cs.fk_catalog_config = lgg.fk_catalog_config
INNER JOIN sales_order_item AS soi ON soi.sku = cs.sku
INNER JOIN sales_order AS so ON soi.fk_sales_order = so.id_sales_order 
INNER JOIN customer as c ON c.id_customer = so.fk_customer
WHERE lgg.fk_catalog_attribute_option_global_gender IN (1,2)
AND so.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
GROUP BY so.fk_customer
HAVING count( lgg.fk_catalog_attribute_option_global_gender ) > 2

I have another table in which there is a column fk_customer and column gender, I need to update that table with the results from above query. I need to do it in the same query. The above query is giving me perfect results.

you can do it with update-join. Here is a link:http://stackoverflow.com/questions/15209414/mysql-update-join. Let me know the columns on which the table to be updated and the select query you have written above needs to be joined on? — Dipendu Paul, Feb 17 '14 at 09:52

score 0 · Accepted Answer · answered Feb 17 '14 at 10:03

You will have to merge the UPDATE statement with this query as:

UPDATE anotherTable A JOIN
(SELECT  so.fk_customer,
IF (((sum( lgg.fk_catalog_attribute_option_global_gender = 1 ) / count(lgg.fk_catalog_attribute_option_global_gender )) *100 > 60) 
AND (c.gender='male'), 'Men',
IF (((sum( lgg.fk_catalog_attribute_option_global_gender = 2 ) / count( lgg.fk_catalog_attribute_option_global_gender )) *100 > 60) 
AND (c.gender='female'), 'Women', '') ) as calculatedGender,
'ID'
FROM catalog_attribute_link_global_gender AS lgg
INNER JOIN catalog_simple AS cs ON cs.fk_catalog_config = lgg.fk_catalog_config
INNER JOIN sales_order_item AS soi ON soi.sku = cs.sku
INNER JOIN sales_order AS so ON soi.fk_sales_order = so.id_sales_order 
INNER JOIN customer as c ON c.id_customer = so.fk_customer
WHERE lgg.fk_catalog_attribute_option_global_gender IN (1,2)
AND so.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
GROUP BY so.fk_customer
HAVING count( lgg.fk_catalog_attribute_option_global_gender ) > 2) B
ON A.'ID' = B.'ID'
SET A.fk_customer = B.fk_customer,
A.gender = B.calculatedGender;

This shall give you the desired result provided you figure out the 'ID' column on which you will join both the tables A and B.

Use results returned from select subquery in update query

1 Answers1