Updating row column with count value of query in same table

Question

I was wondering if somebody could help.

I have a products table and in the table a column called master_colours.

I have 3 columns in the table called stock_id, master and colour, the master column tells it which stock_id to link to like so...

stock_id, master, colour
12345, '', ''
aaa, '12345', 'red'
bbb, '12345', 'blue'
ccc, '12345', 'green'

In the master_colours column I want it to be updated to the value of how many colours the master product will have so in the example above it will be 3.

I have so far the following code which returns the error #1242 - Subquery returns more than 1 row.

UPDATE products AS main
SET main.master_colours = (SELECT master.stock_id FROM (
SELECT p.stock_id, COUNT(DISTINCT(m.colour))
FROM products AS p, products AS m
WHERE m.master = p.stock_id AND m.colour != ''
GROUP BY p.stock_id) AS master)

I understand why it is doing it because I have grouped by the stock_id but if I dont do this then it counts all colours in the table rather than the colours linked to that stock id.

The logical thing to me would be to do this...

UPDATE products AS main
SET main.master_colours = (SELECT master.stock_id FROM (
SELECT p.stock_id, COUNT(DISTINCT(m.colour))
FROM products AS p, products AS m
WHERE m.master = p.stock_id AND m.colour != '' AND p.stock_id = main.stock_id
GROUP BY p.stock_id) AS master)

but obviously this doesnt work because main.stock_id is outside the sub query.

Can anyone please point me in the right direction?

Thanks

Please provide correct schema of products table.

J A
– J A

2014-10-13 16:56:16 +00:00
Commented Oct 13, 2014 at 16:56 — J A
– J A, Commented Oct 13, 2014 at 16:56

peterm · Accepted Answer · 2014-10-13 17:04:05Z

1

Are you looking for something like this?

UPDATE products p JOIN
(
  SELECT master, COUNT(*) total
    FROM products
   GROUP BY master
) q ON p.stock_id = q.master
   SET p.master_colours = total;

That is assuming that you only have 1 level deep hierarchy

Outcome:

| STOCK_ID | MASTER | COLOUR | MASTER_COLOURS |
|----------|--------|--------|----------------|
|    12345 |        |        |              3 |
|      aaa |  12345 |    red |         (null) |
|      bbb |  12345 |   blue |         (null) |
|      ccc |  12345 |  green |         (null) |

Here is SQLFiddle demo

answered Oct 13, 2014 at 17:04

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating row column with count value of query in same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related