MySQL: Update with join in subquery

Question

I got a table with products and a table with reviews of the products. The products-table has the parent- and child-products. The parent-products should get all reviews from the child-products. I did:

DROP TABLE IF EXISTS products;
CREATE TABLE products (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `parent` int(10) unsigned DEFAULT NULL,
    `review` decimal(3,2) DEFAULT NULL,
    PRIMARY KEY(id)
);

DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `product` int(10) unsigned NOT NULL,
    `review` decimal(3,2) DEFAULT NULL,
    PRIMARY KEY(id) 
);

INSERT INTO products SET id=1, parent=null;
INSERT INTO products SET id=2, parent=1;
INSERT INTO products SET id=3, parent=1;

INSERT INTO reviews SET product=2, review=5;
INSERT INTO reviews SET product=3, review=5;
INSERT INTO reviews SET product=3, review=4;

INSERT INTO products SET id=4, parent=null;
INSERT INTO products SET id=5, parent=4;

INSERT INTO reviews SET product=5, review=4;
INSERT INTO reviews SET product=5, review=2;

UPDATE products
SET products.review=
(SELECT SUM(reviews.review)/COUNT(reviews.review) FROM reviews 
LEFT JOIN products p ON p.parent = products.id
)
WHERE products.parent IS NULL;

But with that I'm surprised I'm getting an error:

ERROR 1054 (42S22): Unknown column 'products.id' in 'on clause'

Any suggestions on how to do it correctly? The idea is that product 1 should get a review of 14/3 = 4.66 and product 4 should get a review of 6/2 = 3.

Thank you for asking an SQL question correctly (with SQL needed to reproduce your example DB) <3 I wish more people followed your example. — Amadan, Aug 15 '18 at 10:56
UPDATE products as p1 SET p1.review= (SELECT SUM(reviews.review)/COUNT(reviews.review) FROM reviews where reviews.product = p1.id ) WHERE p1.parent IS NULL; — Murat Tüfekçi, Aug 15 '18 at 11:01

score 3 · Accepted Answer · edited Aug 15 '18 at 12:45

3

The products is not visible in the subquery. Use following syntax instead:

UPDATE products pp
LEFT JOIN (
  SELECT pc.parent, SUM(r.review)/COUNT(r.review) as 'rev'
  FROM reviews r
    LEFT JOIN products pc on r.product = pc.id
  GROUP BY pc.parent
) pcc ON pcc.parent = pp.id  
SET pp.review=pcc.rev
WHERE pp.parent IS NULL;

edited Aug 15 '18 at 12:45

Strawberry

33,750
13
40
57

answered Aug 15 '18 at 10:58

slaakso

8,331
2
16
27

Ildar Akhmetov · Answer 2 · 2018-08-15T10:58:40.763

0

Since you've declared p as an alias for the products table, you need to use it throughout the query. So, in your LEFT JOIN clause just use p.parent instead of products.parent.

UPDATE products
SET products.review=
(SELECT SUM(reviews.review)/COUNT(reviews.review) FROM reviews 
LEFT JOIN products p ON p.parent = p.id
)
WHERE products.parent IS NULL;

edited Aug 15 '18 at 10:58

answered Aug 15 '18 at 10:44

Ildar Akhmetov

1,331
13
22

This would not work as it would not connect the products to be updated with the products in the subquery (=all products would get the same review result) – slaakso Aug 15 '18 at 11:00
@slaakso I assumed that the topic starter intentionally simplified his query. – Ildar Akhmetov Aug 15 '18 at 11:09
Mabye a clarification from @Werner. Also the example data could contain another parent with child rows. – slaakso Aug 15 '18 at 11:40
Thanks for your reply. Sorry, I'm getting "ERROR 1093 (HY000): You can't specify target table 'products' for update in FROM clause" on this (MySQL 5.7.21). – Werner Aug 15 '18 at 11:46
@Werner works just fine for me (MariaDB 10.3). It looks like the MySQL optimizer settings might be an issue for this type of UPDATE..SELECT queries: https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Ildar Akhmetov Aug 15 '18 at 11:54

score -1 · Answer 3 · answered Aug 15 '18 at 11:04

-1

At its heart, you appear to be looking for this value:

SELECT SUM(r.review)/(SELECT COUNT(*) FROM products) n FROM reviews r;
+----------+
| n        |
+----------+
| 4.666667 |
+----------+

So, something like...

UPDATE products x 
  JOIN (SELECT SUM(r.review)/(SELECT COUNT(*) FROM products) n FROM reviews r) y
   SET x.review = y.n
 WHERE x.review IS NULL;

answered Aug 15 '18 at 11:04

Strawberry

33,750
13
40
57

That would put same calculated for all rows (regardless of it being a parent or a child). As the there is no connection from subquery to main updateadble table (other that the x.review connection, a typo?) all rows will get the same value. Understood that the purpose was to do calculation for each parent based on their child values. – slaakso Aug 15 '18 at 11:39
Thanks for your reply. Yes, unfortunately this gives a review of 4 to every product in my example. – Werner Aug 15 '18 at 11:52
This gives a review to every null value only. – Strawberry Aug 15 '18 at 12:43

MySQL: Update with join in subquery

3 Answers3