0

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 3rd 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:

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 = ?;
Noah Overcash
  • 121
  • 3
  • 14

1 Answers1

2

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • The code I shared (`UPDATE \`cos\` INNER JOIN \`co_types\` ON (\`cos\`.\`TYPE_ID\`=\`co_types\`.\`ID\`) SET \`cos\`.\`ARCHIVED\` = ? WHERE \`co_types\`.\`A_ID\` = ?;`) was exactly what I ran. – Noah Overcash Feb 10 '18 at 02:02
  • What happens if you change it to `WHERE A_ID = ?`? – Barmar Feb 10 '18 at 02:05
  • I receive the same `SQLSTATE[42S22]: Column not found: 1054 Unknown column 'co_types.A_ID' in 'where clause'`, which seems strange as I'm not even fully qualifying the column name. – Noah Overcash Feb 10 '18 at 02:09
  • Yes, that's very strange. How can it know that it shouldn't complain about `cos.A_ID`? Can you turn on query logging in mysqld and see what it logs? See https://stackoverflow.com/questions/6479107/how-to-enable-mysql-query-log – Barmar Feb 10 '18 at 02:12
  • Are there any views involved? – Barmar Feb 10 '18 at 02:13
  • I hadn't even thought to look at the general log. Looking at that, I realize I've been an idiot. I had been trying a bunch of things for the past few hours, and, since the first error with the UPDATE query, assumed all further errors were from it. Looking at the general log I see it was several later, and that I had fixed my UPDATE query. I'll post my solution up above and mark your answer as correct. Thanks for your help! – Noah Overcash Feb 10 '18 at 02:15