Question 21252 in Mysql Question posted in
The official MySQL documentation can be found here.

Set Count Values from one table to Another – Mysql

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?

Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search