MariaDB 1064: syntax error near WHERE clause on UPDATE

When does this error appear?

You're writing an UPDATE statement that joins another table — maybe you're syncing prices from a staging table into the main products table. Your SQL looks something like this:

UPDATE products p
JOIN price_updates pu ON p.id = pu.product_id
SET p.price = pu.new_price
WHERE p.category = 'electronics' AND status = 'active';

MariaDB throws back Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE p.category = 'electronics' AND status = 'active''. The error points at the WHERE clause, but the real problem is earlier in the query.

Root cause

What's actually happening here is the MariaDB parser can't figure out which table status belongs to. You used an alias p for the products table, but then in the WHERE clause you wrote status = 'active' without any table prefix. The parser sees p.category (prefixed) and then status (unprefixed) and gets confused — it can't resolve the column reference because the alias changed the table name scope.

The error message is misleading. It says syntax error near WHERE, but the actual cause is an ambiguous column reference. MariaDB's parser is stricter than MySQL's in some edge cases, especially when you mix aliases and unqualified column names in multi-table UPDATE statements.

This happens most often when you copy a query from a single-table UPDATE and add a JOIN without updating all column references.

How to fix it

1. Prefix every column in the WHERE clause with the correct table alias. Even if you think the column is unique across all joined tables, be explicit. MariaDB's parser will thank you.

   UPDATE products p
   JOIN price_updates pu ON p.id = pu.product_id
   SET p.price = pu.new_price
   WHERE p.category = 'electronics' AND p.status = 'active';

2. Check the SET clause too. If you're setting a column that exists in both tables, prefix it. In the example above, p.price is fine, but if you wrote SET price = pu.new_price without a prefix, you'd get the same 1064.
3. If you're using a subquery, wrap it in parentheses and give it an alias. For example:

   UPDATE products p
   SET p.price = (
     SELECT pu.new_price
     FROM price_updates pu
     WHERE pu.product_id = p.id
   )
   WHERE p.category = 'electronics';

   The subquery must have its own alias (pu here) and you must reference the outer table with its alias (p.id).
4. Test the SELECT equivalent first. Before running the UPDATE, write the same join as a SELECT to verify column references:

   SELECT p.price, pu.new_price
   FROM products p
   JOIN price_updates pu ON p.id = pu.product_id
   WHERE p.category = 'electronics' AND p.status = 'active';

   If this runs, your UPDATE will work too — just swap SELECT for UPDATE and add SET.

What if it still fails?

If the error persists after adding aliases, three things to check:

• MariaDB version. Versions before 10.2 had stricter alias rules. Run SELECT VERSION();. If you're on 10.1 or older, consider upgrading — those versions are end-of-life and have known parser bugs.
• Reserved keywords. If your column or table name is a reserved word (like status, order, group), backtick-quote it: `status`. But actually, using aliases properly makes this unnecessary.
• Missing JOIN condition. If you write JOIN price_updates pu without an ON clause, MariaDB will also throw 1064 but at a different position. Always include the join condition.

The fix is simple once you know the pattern: never leave a column unqualified in a multi-table UPDATE. Took me three hours to figure this out the first time on a production database at 2 AM. Don't be me.