Search code examples
mysqlsqlmysql-error-1054

SQL Query Problem

I have the following query that is failing and I don't understand why. Clearly the column that the code is complaining about is in the table.

Query:

INSERT
  INTO `ProductAudit` (`Id`, `Field`, `OldValue`, `NewValue`, `ChangedOn`)
SELECT t.`ProductId`, 'Title', p.`Title`, t.`Title`, t.`ProcessedOn`
  FROM `TempImport` t
  LEFT JOIN `Product` p
    ON t.`ProductId` = p.`Id`
 WHERE p.`Title` != t.`Title`
    ON DUPLICATE KEY UPDATE
       p.`ChangedOn` = VALUES(`ChangedOn`)

Table:

CREATE TABLE `ProductAudit` (
  `Id` varchar(32) NOT NULL,
  `Field` varchar(16) NOT NULL,
  `OldValue` text,
  `NewValue` text,
  `ChangedOn` date NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Error:

Error Code: 1054 - Unknown column 'p.ChangedOn' in 'field list'
Solution

  • The table you list the CREATE statement for is not the same table you are referencing in the query. P is the Product table, not the ProductAudit table.