SQL Syntax Error - updating while joining two tables

Question

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!

#1064 - 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 'JOIN exp_store_order_items ON exp_store_orders.id = exp_store_order_items.ord' at line 3 — Colton Neifert
– Colton Neifert, Commented Jan 6, 2015 at 2:17
Is there a field_id_49 ? Also, it's a little odd to group by the thing that you're counting !?!?! If it was me, I'd start over with some proper DDLs and a desired result. — Strawberry
– Strawberry, Commented Jan 6, 2015 at 2:25
So here's what I'm trying to do. I need a way to mark orders on our store which are purely digital content. Digital items have a url in field_id_50, there can be multiple items per order, so I'm counting the number or items with a URL and comparing that number to the total number of items in the order. — Colton Neifert
– Colton Neifert, Commented Jan 6, 2015 at 2:29

Gordon Linoff · Accepted Answer · 2015-01-06 02:43:46Z

1

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';

edited Jan 6, 2015 at 2:43

answered Jan 6, 2015 at 2:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Colton Neifert Over a year ago

This seems like it's going to work, could you please explain what's going on with the 'ou' after the subquery? It's giving me this: #1054 - Unknown column 'ou.id' in 'on clause'

Colton Neifert Over a year ago

It's supposed to be seeing the subquery as another table that it's joining with correct? I wonder why it's not.

dar7yl · Accepted Answer · 2015-01-06 02:44:26Z

0

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]

answered Jan 6, 2015 at 2:44

dar7yl

3,78528 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL Syntax Error - updating while joining two tables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related