Set Count Values from one table to Another-CodePudding

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

....... 100 More Customers

Table "Summary":

ID	Customer	Count
1	John	2
2	Mary	2

......... 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?

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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.