Mysql: Count Records from One Table and Then Update Another

MySQL: Count records from one table and then update another

Use a subquery:

UPDATE nations 
SET count = (
SELECT COUNT(id)
FROM poets
WHERE poets.nation = nations.id
GROUP BY id
);

MySQL: UPDATE table with COUNT from another table?

If your num column is a valid numeric type your query should work as is:

UPDATE tbl1 SET num = (SELECT COUNT(*) FROM tbl2 WHERE id=tbl1.id)

Update table based on a count of records in another table

Use a CTE or subquery to get the winner counts by EmployeeKey for recent events. Next, join this CTE with EventEntries and filter EventEntries to future events only. You'll now have enough information in context to set Priority according to your rules.

--!!! Please backup your data before running the update, or do it as a transaction and test the result before committing. !!!

WITH [recent-event-winner-counts] AS (
SELECT [EmployeeKey], COUNT(*) AS [Occurrences]
FROM [EventWinners] AS [w]
INNER JOIN [EventDescriptions] AS [d]
ON [w].[EventID] = [d].[EventID]
WHERE [StartDateTime] BETWEEN DATEADD(DAY, -90, GETDATE()) AND GETDATE()
GROUP BY [EmployeeKey]
)
UPDATE [e]; -- <- remove this semicolon when you're ready to run this
SET Priority = CASE
WHEN [Occurrences] IS NULL THEN 1
WHEN [Occurrences] = 1 THEN 2
WHEN [Occurrences] >= 2 THEN 3
ELSE Priority -- leave unchanged
END
FROM [EventEntries] AS [e]
INNER JOIN [EventDescriptions] AS [d]
ON [e].[EventID] = [d].[EventID]
-- left join as we don't care about EmployeeKeys exclusively in EventWinners
LEFT JOIN [recent-event-winner-counts] AS [r]
ON [e].[EmployeeKey] = [r].[EmployeeKey]
WHERE [d].[StartDateTime] > GETDATE(); -- future events only

How to select data from one table and select count to another table and combine it in MySQL?

If I have the following tables:

fruit

id    name
1 apple
2 orange

tickets

id  fruit_id
1 1
2 1
3 2
4 2
5 2

Then I would use the following SQL syntax to output a table like the one you need:

SELECT fruit.id, fruit.name, COUNT(tickets.id)
FROM tickets
LEFT JOIN fruit ON fruit.id = tickets.fruit_id
GROUP BY fruit.id;

Output:

id     name   COUNT(tickets.id)
1 apple 2
2 orange 3

MySQL Summing & Counting Child Values in Another Table

I figured i could make us of COUNT() to count all rows in BOOKING_PAYMENTS_TABLE but then how can i get the number for the rows that have an mxnAMOUNT?

Just COUNT() that particular column: this gives you the number of non-null values in the column for each group:

SELECT b.*, SUM(p.mxnAmount), COUNT(p.id), COUNT(p.mxnAmount)
FROM bookings b
LEFT JOIN bookingPayments p ON b.id = p.bookingId
GROUP BY b.id

If you want to know if any mxmamount in the group is missing, you can do:

MAX(p.id IS NOT NULL AND p.mxnAmount IS NULL) has_missing_mxnAmount


Related Topics



Leave a reply



Submit