CODEWITHSUNDEEP
mysqlsqljoinsql-updatesyntax-error
Colton Neifert
Colton Neifert

Reputation: 83

SQL Syntax Error - updating while joining two tables

Could anyone help me understand why I'm getting a syntax error when trying to run this query?

UPDATE exp_store_orders
SET exp_store_orders.order_status_name = "Digital"
    JOIN exp_store_order_items
        ON exp_store_orders.id = exp_store_order_items.order_id
    JOIN exp_channel_data
        ON exp_store_order_items.entry_id = exp_channel_data.entry_id
GROUP BY exp_store_order_items.order_id
HAVING COUNT(CASE exp_channel_data.field_id_50 WHEN '' THEN null ELSE 1 END) =     COUNT(exp_store_order_items.order_id)

This brings up the ID's for the orders I'd like to update, but for some reason the above kicks back a syntax error.

SELECT exp_store_orders.id
FROM exp_store_orders
    JOIN exp_store_order_items
        ON exp_store_orders.id = exp_store_order_items.order_id
    JOIN exp_channel_data
        ON exp_store_order_items.entry_id = exp_channel_data.entry_id
GROUP BY exp_store_order_items.order_id
HAVING COUNT(CASE exp_channel_data.field_id_50 WHEN '' THEN null ELSE 1 END) =     COUNT(exp_store_order_items.order_id)

Any help is appreciated, thanks!

Upvotes: 1

Views: 85

Answers (2)

dar7yl
dar7yl

Reputation: 3757

You are missing the WHERE clause in your statement.

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270633

The proper syntax in MySQL for an update with join is to have the join before the set. In addition, group by is not allowed in an update.

Instead, get the list from the subquery and use another join to set the rows. The subquery doesn't actually need exp_store_orders, so that can be removed:

UPDATE exp_store_orders o JOIN
       (SELECT oi.order_id
        FROM exp_store_order_items oi JOIN
             exp_channel_data cd
             ON oi.entry_id = cd.entry_id
        GROUP BY oi.order_id
        HAVING COUNT(CASE cd.field_id_50 WHEN '' THEN null ELSE 1 END) = COUNT(oi.order_id)
       ) ou
       on o.id = ou.order_id
    SET o.order_status_name = 'Digital';

Upvotes: 1

Related Questions