mysql - 连接查询时更新查询语法

Question

我有一个查询，可以提取我们系统中交易总数为 0 且 contact_status_c = 客户 的所有联系人。查询如下:

SELECT
contacts.first_name,
contacts.last_name,
contacts.phone_home,
contacts.phone_mobile,
contacts.phone_work,
contacts.primary_address_street,
contacts.primary_address_city,
contacts.primary_address_state,
contacts.primary_address_postalcode,
contacts.primary_address_country,
opportunities_cstm.stage_c,
contacts_cstm.contact_status_c,
SUM(opportunities_cstm.stage_c = 'Pending'
OR opportunities_cstm.stage_c = 'AccountActive'
OR opportunities_cstm.stage_c = 'Reinstated'
OR opportunities_cstm.stage_c = '%Maturity%'
OR opportunities_cstm.stage_c = 'Flagged'
OR opportunities_cstm.stage_c = 'Stalled') AS deal_count
FROM
opportunities
JOIN opportunities_cstm
ON opportunities.id = opportunities_cstm.id_c
RIGHT JOIN contacts
ON opportunities_cstm.contact_id_c = contacts.id
JOIN contacts_cstm
ON contacts_cstm.id_c = contacts.id
WHERE
contacts.deleted = 0
AND opportunities.deleted = 0
AND contacts_cstm.contact_status_c <> 'Lost Client'
GROUP BY
contacts.id
HAVING deal_count = 0
ORDER BY
contacts.first_name ASC,
contacts.last_name ASC

它提取了 175 个联系人。现在，我尝试更新 contacts_cstm 表中的所有 contact_status_c 记录，以反射(reflect)所有 175 个客户均被列为 Lost Clients而不是客户端。问题就出在这里。

最初，我只是尝试使用基本的 Update 语句来执行此操作，但由于它太宽泛，它更新了 1881 条记录而不是 175 条。因此，我得出结论，我需要包含 group by 和having。使用以下位置提供的信息:

https://stackoverflow.com/questions/8793914/using-a-having-clause-in-an-update-statement

(这是最有用的)以及其他几篇论坛帖子和文章，我设法写下了这一声明:

UPDATE
    c_1
FROM
    contacts_cstm c_1
    JOIN (
        SELECT
            contacts.first_name,
            contacts.last_name,
            contacts.phone_home,
            contacts.phone_mobile,
            contacts.phone_work,
            contacts.primary_address_street,
            contacts.primary_address_city,
            contacts.primary_address_state,
            contacts.primary_address_postalcode,
            contacts.primary_address_country,
            opportunities_cstm.stage_c,
            contacts_cstm.contact_status_c,
            SUM(opportunities_cstm.stage_c = 'Pending'
            OR opportunities_cstm.stage_c = 'AccountActive'
            OR opportunities_cstm.stage_c = 'Reinstated'
            OR opportunities_cstm.stage_c = '%Maturity%'
            OR opportunities_cstm.stage_c = 'Flagged'
            OR opportunities_cstm.stage_c = 'Stalled') AS deal_count
        FROM
            Contacts_Cstm 
        RIGHT JOIN contacts
            ON opportunities_cstm.contact_id_c = contacts.id
        JOIN opportunities_cstm
            ON opportunities.id = opportunities_cstm.id_c
        JOIN opportunities
            ON opportunities_cstm.id_c = opportunities.id
        WHERE
            contacts.deleted = 0
            AND opportunities.deleted = 0
            AND contacts_cstm.contact_status_c <> 'Lost Client'
        GROUP BY
            contacts.id
        HAVING 
            deal_count = 0
    ) contacts_cstm as c_2
    ON
        c_1.id_c = c_2.id_c
    SET 
        contact_status_c = 'Lost Client'

不幸的是，它抛出了一个“简单”语法错误:

Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM
    contacts_cstm c_1
    JOIN (
        SELECT
            contacts.first_name,
            contacts.las' at line 3

我广泛地查阅了文献和这里的几篇文章，但无法弄清楚这里出了什么问题。我有一种感觉，它与它抛出的错误没有任何关系。

我们非常感谢任何和所有的帮助。预先感谢您!

Answer 1

我的系统上没有安装MySQL，所以我无法测试它，但它应该类似于下面的查询。另外，您没有给出表结构，因此我假设您有一个 id 列。

UPDATE contacts_cstm a
JOIN (
    SELECT contacts.first_name,
        contacts.last_name,
        contacts.phone_home,
        contacts.phone_mobile,
        contacts.phone_work,
        contacts.primary_address_street,
        contacts.primary_address_city,
        contacts.primary_address_state,
        contacts.primary_address_postalcode,
        contacts.primary_address_country,
        opportunities_cstm.stage_c,
        contacts_cstm.id_c
        contacts_cstm.contact_status_c,
        SUM(opportunities_cstm.stage_c = 'Pending' OR opportunities_cstm.stage_c = 'AccountActive' OR opportunities_cstm.stage_c = 'Reinstated' OR opportunities_cstm.stage_c = '%Maturity%' OR opportunities_cstm.stage_c = 'Flagged' OR opportunities_cstm.stage_c = 'Stalled') AS deal_count
    FROM opportunities
    JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
    RIGHT JOIN contacts ON opportunities_cstm.contact_id_c = contacts.id
    JOIN contacts_cstm ON contacts_cstm.id_c = contacts.id
    WHERE contacts.deleted = 0 AND opportunities.deleted = 0 AND contacts_cstm.contact_status_c <> 'Lost Client'
    GROUP BY contacts.id
    HAVING deal_count = 0
) b ON b.id_c = a.id_c
SET a.contact_status_c = 'Lost Client'