Unable to use JOIN in an UPDATE query with PDO

Question

I've tried every way I can think of, and, according to my research, that there is to use a JOIN statement in an UPDATE query.

UPDATE
    `cos` 
INNER JOIN
    `co_types` 
        ON (
            `cos`.`TYPE_ID`=`co_types`.`ID`
        )  
SET
    `cos`.`ARCHIVED` = ? 
WHERE
    `co_types`.`A_ID` = ?;

I am using PDO, calling execute with [1, 1] as parameters. However, in doing so, I get the following error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'co_types.A_ID' in 'where clause'

I've tried rearranging the query every way I can from what I've found, including:

• Adding a FROM clause
• Reordering the clauses around
• Linting my query (success)
• Going to the 3^rd page of google
• And some other stuff I don't quite remember after 4 hours of this...

The above query is a formatted version, however the raw query being sent is no different except whitespace:

UPDATE `cos` INNER JOIN `co_types` ON (`cos`.`TYPE_ID`=`co_types`.`ID`)  SET `cos`.`ARCHIVED` = ? WHERE `co_types`.`A_ID` = ?;

Taking either the minified or formatted versions, inserting values manually, and running them in a client such as MySQL's command line or PHPMyAdmin is successful.

Below are my table definitions, shortened for brevity:

CREATE TABLE `cos` (
  `ID` int(11) UNSIGNED NOT NULL,
  `USER_ID` int(11) UNSIGNED NOT NULL,
  `TYPE_ID` int(11) UNSIGNED NOT NULL,
  `ARCHIVED` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `co_types` (
  `ID` int(11) UNSIGNED NOT NULL,
  `A_ID` int(11) UNSIGNED NOT NULL,
  `NAME` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

There are more columns and whatnot, however I do not believe they are relevant to the question.

Here are some of the websites I used when initially trying to debug this issue:

• MYSQL Update Statement Inner Join Tables
• sql update with inner join and where
• mysql update query with inner join
• MySql Update A Joined Table
• http://www.mysqltutorial.org/mysql-update-join/
• https://forums.mysql.com/read.php?6,156225,156271
• https://dba.stackexchange.com/questions/21152/how-to-update-one-table-based-on-another-tables-values-on-the-fly/36664
• https://dba.stackexchange.com/questions/41261/update-table-based-on-the-same-table
• http://www.voidtricks.com/mysql-inner-join-update/
• https://www.electrictoolbox.com/article/mysql/cross-table-update/
• https://makandracards.com/makandra/32357-postgresql-vs-mysql-how-to-update-using-a-join
• https://chartio.com/resources/tutorials/how-to-update-from-select-in-sql-server/
• http://www.geeksengine.com/database/data-manipulation/cross-table-update.php
• https://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/
• http://emrpms.blogspot.com/2014/12/mysql-update-example-using-group-by-and.html
• http://www.tech-recipes.com/rx/56823/delete-update-rows-using-inner-join-sql-server/
• https://www.sqlservercentral.com/Forums/Topic307916-8-1.aspx

However, most of these were somewhat irrelevant. Regardless, I attempted most all variations that I could.

If anyone could shed some light on why this doesn't work, that'd be great!

---

Solution:

Unqualifying the column names fixed my issue:

UPDATE
    `cos` 
INNER JOIN
    `co_types` 
        ON (
            `cos`.`TYPE_ID`=`co_types`.`ID`
        )  
SET
    `cos`.`ARCHIVED` = ? 
WHERE
    A_ID = ?;

Answer 1

I'll bet your actual code looks like this:

WHERE `co_types.A_ID` = ?`

Notice that it has the backticks around both the table and column names, rather than around each of them separately. This prevents the special meaning of the . character as a separator between table and column, treating it as a literal character in the column name.

It should be

WHERE `co_types`.`A_ID` = ?

as in the question. Or just leave out the backticks entirely, they're not needed when the names don't contain special characters.