menu

Questions & Answers

Set Count Values from one table to Another

I am trying to count matching values from customer column on table 'Customers' and update their values on the Count Column in table 'Summary'. I also want to Check if the Date is <= Todays Date.

Table "Customers":

ID Customer Date
1 John 2022-01-01
2 John 2022-01-01
3 Mary 2022-01-01
4 Mary 2022-01-01

.......+2000 More Customers

Table "Summary":

ID Customer Count DateInput
1 John 2 2021-01-01
2 Mary 2 2021-01-01

.........+100 More Customers

I can update one row at a time like this:

update Summary
set Count = (SELECT COUNT(*) 
             FROM Customers 
             WHERE Customer = "John" AND Date <=CURRENT_DATE()) 
WHERE Customer = "John";

Is there a way to use the above query to update the count column for John, mary, etc, etc without doing Multiple individual requests?

Answers(3) :

You can do it as follows :

UPDATE Summary s
INNER JOIN (
  SELECT Customer, count(1) as _count
  FROM Customers
  where Date <=CURRENT_DATE()
  group by Customer
) as c on s.Customer = c.Customer
set s.Count = c._count ;

I have used inner join to join a list of customers and their counts. and the relation is Customer.

Comments:
2023-01-17 00:48:39
This returns the same error as data03 suggestion. "#1442 - Can't update table 'Customers' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." Thanks for the efforts guys, just cant seem to get it to work. I have even tried an event to update every minute to avoid that error but the numbers dont update.
2023-01-17 00:48:39
Seems like you are trying to use this query in a function/trigger ! Can you share it
2023-01-17 00:48:39
This actually works if i just make a simple table like i described. The problem is that i have a date that i manually input on summary.dateinput and it updates customer.date. via a trigger. after that happens i want the above that i described to also update. without the trigger it works.
2023-01-17 00:48:39
CREATE TRIGGER test AFTER UPDATE ON summary FOR EACH ROW update customer set Date = DATE_ADD(Date, INTERVAL +1 year) FROM summary WHERE Customer = "John") WHERE Customer = "John" That is the trigger I am using. I would like to know how to have that also work for every customer without inputting multiple triggers

Given that your count values will change, you should consider creating a view instead of updating a table:

CREATE VIEW summary AS 
SELECT ID, Customer, COALESCE(COUNT(CASE WHEN Date <= CURRENT_DATE() THEN 1 END), 0) AS cnt
FROM Customers
GROUP BY ID, Customer

If you really want to have a table and update it every time, you need such UPDATE statement:

WITH cte AS (
    SELECT ID, Customer, COUNT(*) AS count
    FROM Customers 
    WHERE Date <= CURRENT_DATE()
    GROUP BY ID, Customer
)
UPDATE Summary
INNER JOIN cte ON Summary.ID = cte.ID AND Summary.Customer = cte.Customer
SET Summary.count = cte.count
Comments:
2023-01-17 00:48:39
I Dont know what i am doing wrong but i get "MySQL returned an empty result set (i.e. zero rows). (Query took 0.0197 seconds.)"
2023-01-17 00:48:39
The UPDATE statement returns no rows. Try selecting what's in the table right after you update.

Is this something you are looking for?

UPDATE
  Summary s
  INNER JOIN Customers c ON s.Customer = c.Customer
SET
  s.Count = (
    SELECT
      COUNT(*)
    FROM
      Customers c2
    WHERE
      c2.Customer = s.Customer
      AND c2.Date <= CURRENT_DATE()
  )

If you are going to test the query, please test it on a small dataset before applying it to the entire table since it may not achieve the results you are expecting.

Comments:
2023-01-17 00:48:40
#1442 - Can't update table 'Customers' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
2023-01-17 00:48:40
Ok when I remove my trigger this works great. Based upon this I made a new trigger. When I update summary.dateinput it changes the customer dates that matches + 1 year. This also works very well. Here is the query that I used for the trigger --- UPDATE customers w INNER JOIN summary sub ON w.Customer = sub.Customer SET w.Count = (SELECT DATE_ADD(DateInput, INTERVAL +1 year) FROM summary sub2 WHERE sub2.Customer = sub.customer) . Works great. Thank you. My only issue now is having the 2 queries on a trigger.
2023-01-17 00:48:40
I'm happy to hear that you found a solution and that I could help! You could use a temporary table or subquery to store the results of the join before performing the update, maybe that could help.