技术问询：如何将目标表字段设为另一表邮箱地址的统计数？

Hey there! Let's get this sorted out for you. Your current query has a couple of issues that are keeping it from working correctly—let's break them down first, then jump to the right solutions.

What's Wrong with Your Current Query? #

• The syntax a.PickedUp_Count AS COUNT(b.Emailaddress) is invalid. You can't assign an aggregate function directly like that in a SELECT without grouping.
• Without a GROUP BY clause, your query will return duplicate rows from Master_List for every matching entry in Picked_Up, instead of a single row per email with the total count.
• Using INNER JOIN will only include emails that exist in both tables, which means any emails in Master_List with no matches in Picked_Up will be excluded (probably not what you want if you need to set their count to 0).

Solution 1: Update the Master_List Table's PickedUp_Count Field #

If your goal is to permanently update the field in Master_List to reflect the total occurrences in Picked_Up, use one of these approaches:

Option A: Subquery Update (Works in Most Databases)

This approach calculates the count for each email directly in the update statement, and uses COALESCE to make sure emails with no matches get a count of 0 instead of NULL:

UPDATE Master_List a
SET a.PickedUp_Count = COALESCE(
    (SELECT COUNT(*)
     FROM Picked_Up b
     WHERE b.Emailaddress = a.Emailaddress), 0
);

Option B: Join with Aggregated Subquery (More Efficient for Large Data)

First pre-aggregate the counts from Picked_Up, then join to Master_List to update. This is faster if you have a lot of data:

UPDATE Master_List a
LEFT JOIN (
    SELECT Emailaddress, COUNT(*) AS PickCount
    FROM Picked_Up
    GROUP BY Emailaddress
) b ON a.Emailaddress = b.Emailaddress
SET a.PickedUp_Count = COALESCE(b.PickCount, 0);

Solution 2: Query the Count Without Updating the Table #

If you just want to view the count alongside your Master_List data (not modify the table), use this query:

SELECT 
    a.*,
    COALESCE(b.PickCount, 0) AS PickedUp_Count
FROM Master_List a
LEFT JOIN (
    SELECT Emailaddress, COUNT(*) AS PickCount
    FROM Picked_Up
    GROUP BY Emailaddress
) b ON a.Emailaddress = b.Emailaddress;

The LEFT JOIN ensures all entries from Master_List are included, even if there are no matches in Picked_Up (their count will show as 0).

内容的提问来源于stack exchange，提问作者JaylovesSQL