I have table named orders:

| order_id | user_id |
| 91881 | a001 |
| 82191 | a002 |
| 73817 | a001 |
| 91289 | a003 |
| 81828 | a002 |
| 82917 | a002 | 

I want to create a new table which consist unique user_id and how many orders have been put by the user.

| user_id | total_orders |
| a001 | 2 |
| a002 | 3 |
| a003 | 1 |

For the new table I wrote

CREATE TABLE new_table AS (SELECT DISTINCT user_id FROM orders);
ALTER TABLE new_table
    ADD (total_orders INT)

The problem occurs when I try to update the total_orders column by write this code

UPDATE new_table
SET new_table.total_orders = (
SELECT COUNT(DISTINCT(order_id)) FROM orders
GROUP BY user_id
ORDER BY user_id ASC)
WHERE new.user_id = (SELECT DISTINCT user_id FROM orders);

and results in "Subquery returns more than 1 row"

What should be changed in the update code?

Thanks in advance.

3 Answers

1
Zohar Peled On Best Solutions

There's your problem: WHERE new.user_id = (SELECT DISTINCT user_id FROM orders);

You can probably use update with a join to do it:

UPDATE new_table as t0
JOIN (
    SELECT user_id, 
    COUNT(DISTINCT order_id) As total_orders 
    FROM orders 
    GROUP BY user_id
) as t1
    ON t0.user_id = t1.user_id 
SET t0.total_orders = t1.total_orders

However, I suspect this entire update is wrong to begin with. Usually, storing values that can easily be computed on the fly is a mistake - especially if they are a result of a computation on a different table. Having that total_orders column means you have to keep synchronizing it against the actual data in the orders table - so you need triggers on that table for update, insert and delete - seems like a simple view containing the data you want is a better solution.

1
Nick On

You can just use aggregation in your original CREATE TABLE command to create the total_orders column at the same time:

CREATE TABLE new_table AS
SELECT user_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY user_id;

SELECT * FROM new_table

Output:

user_id total_orders
a001    2
a002    3
a003    1

However this is static and does not update as a user creates more orders. It probably makes more sense to create a VIEW (exactly the same query, just replace TABLE with VIEW):

CREATE VIEW new_table AS
SELECT user_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY user_id

This will give updated results as more orders are added to the orders table.

Demo on dbfiddle

0
Gordon Linoff On

You just need a correlated subquery:

UPDATE new_table nt
    SET nt.total_orders = (SELECT COUNT(DISTINCT o.order_id))
                           FROM orders o
                           WHERE o.user_id = nt.user_id
                          )
    WHERE nt.user_id IN (SELECT o.user_id FROM orders o);

However, I am guessing that order_id is unique in orders. Also, you probably want to set the value to 0 if there are no orders.

So:

UPDATE new_table nt
    SET nt.total_orders = (SELECT COUN(*)
                           FROM orders o
                           WHERE o.user_id = nt.user_id
                          );