Update Query Syntax When JOINing on a Query

Question

I have a query that pulls all of the contacts that have 0 total deals in our system and contact_status_c = Client. The query is as follows:

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

It pulls 175 contacts. Now I'm trying to update all of the contact_status_c records in the contacts_cstm table to reflect that all 175 of these clients are listed as Lost Clients instead of Clients. Herein lies the issue.

Originally, I just tried to use a basic Update statement to do this, but because it was too broad, it updated 1881 records instead of the 175. Thus, I concluded I needed to include group by and having. Using the information provided at:

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

(this one being the most useful) and several other forum posts and articles I managed to write this 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'

Unfortunately, it's throwing a "simple" syntax error:

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

I have looked extensively through literature and over several of the posts on here, but have been unable to figure out what's going wrong here. I have a feeling it doesn't have anything to do with the error it's throwing.

Any and all help is definitely appreciated. Thank you in advance!

Ah.. Makes sense. Although, admittedly, I'm not very familiar with the differences between the language. Thanks for pointing that out! — Anthony Meyer, Mar 02 '18 at 15:32

score 0 · Accepted Answer · answered Mar 01 '18 at 22:14

0

I don't have MySQL installed on my system so I can't test it, but it should be similar to the query below. Also you don't give a table structure so I assume you have an id column.

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'

answered Mar 01 '18 at 22:14

Eric

3,165
1
19
25

There's no need for all the columns in the `SELECT` list of the subquery. The query optimizer will almost certainly ignore them. – Barmar Mar 01 '18 at 22:25
@Barmar It has `GROUP BY` and `HAVING`. I don't know how it will work out so I wrap the whole query in subquery, which probably is not necessary. – Eric Mar 01 '18 at 22:28
You only need to select the column with the alias in the `HAVING` clause. You're not even selecting `contacts.id`, which is in the `GROUP BY`. – Barmar Mar 01 '18 at 22:31
Sorry for the delayed response. This worked like a charm! What was I missing though? Was it just the overall structure that was wrong? – Anthony Meyer Mar 02 '18 at 15:31
@AnthonyMeyer In MySQL, when updating, you have to use the table name. `UPDATE tableA_name JOIN tableB_name SET ...`. In SQL Server, you do `UPDATE tableA_alias SET ... FROM tableA_name tableA_alias JOIN tableB_name tableB_alias ...` You are definitely using SQL Server syntax since I see the table alias and the `FROM` clause. – Eric Mar 02 '18 at 16:42
Gotcha. Thank you for making the distinction! And thank you for your replies! – Anthony Meyer Mar 05 '18 at 16:37

Update Query Syntax When JOINing on a Query

1 Answers1