关联子查询时的UPDATE语句语法问题求助

Got it, let's sort out this update mishap! The issue here is that your initial UPDATE statement only filtered on contact_status_c = 'Client' without adding the critical "total transactions = 0" condition, which is why it updated 1881 records instead of your target 175.

Here are two reliable approaches to get this right, assuming your main contacts table is contacts, the transactions table is transactions (linked via contact_id), and contacts_cstm uses id_c to reference contacts.id (a common pattern in tools like SuiteCRM):

1. Use a Subquery to Target Exact Records #

This method first identifies the IDs of contacts that meet both your criteria, then updates only those corresponding records in contacts_cstm:

UPDATE contacts_cstm
SET contact_status_c = 'Lost Clients'
WHERE id_c IN (
    SELECT c.id
    FROM contacts c
    INNER JOIN contacts_cstm cstm ON c.id = cstm.id_c
    WHERE cstm.contact_status_c = 'Client'
    AND (SELECT COUNT(*) FROM transactions t WHERE t.contact_id = c.id) = 0
);

2. Use JOIN for More Efficient Updates #

Joining tables directly can be faster for large datasets, and lets you verify the count easily before updating:

First, Verify the Target Record Count (Always Do This First!) #

Before running any UPDATE, run this SELECT to confirm you're targeting exactly 175 records:

SELECT COUNT(DISTINCT cstm.id_c) AS target_records
FROM contacts_cstm cstm
INNER JOIN contacts c ON c.id = cstm.id_c
LEFT JOIN transactions t ON t.contact_id = c.id
WHERE cstm.contact_status_c = 'Client'
GROUP BY c.id, cstm.id_c
HAVING COUNT(t.id) = 0;

Then Run the UPDATE #

Once you confirm the count matches 175, execute the update:

UPDATE contacts_cstm cstm
INNER JOIN contacts c ON c.id = cstm.id_c
LEFT JOIN transactions t ON t.contact_id = c.id
SET cstm.contact_status_c = 'Lost Clients'
WHERE cstm.contact_status_c = 'Client'
GROUP BY c.id, cstm.id_c
HAVING COUNT(t.id) = 0;

Key Notes: #

• Replace transactions and contact_id with your actual transaction table name and linking column if they differ.
• The LEFT JOIN + COUNT(t.id) = 0 ensures we only select contacts with no related transactions (since COUNT ignores NULL values from the left join).

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