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
Get the First and Last Date of Next Month in MySQL
How to Execute a Native SQL Script in JPA/Hibernate
SQL Query for Getting Data for Last 3 Months
Timestamp Difference in Hours for Postgresql
In Oracle, How to Insert or Update a Record Through a View
Query Times Out from Web App But Runs Fine from Management Studio
Group Datetime into 5,15,30 and 60 Minute Intervals
Ora-00972 Identifier Is Too Long Alias Column Name
Get Avg Ignoring Null or Zero Values
Why Don't Dbms's Support Assertion
Getting SQL Server Cross Database Dependencies
Is There Any Query for Cassandra as Same as SQL:Like Condition
Sql: Select Rows with a Column Value That Occurs at Least N Times
Temporal Database Design, with a Twist (Live VS Draft Rows)