Forum

I am trying to write a custom SQL query here. The query below works in MySQL Workbench or Sequel Pro, but when I try to use it in the CMS Builder project using PHP I get an error. I changed the table names and column names, but this is the query:

$deleteQuery = "DELETE FROM `cmsb_my_table` AS mt WHERE recent_date NOT IN ( SELECT MAX(recent_date) FROM (SELECT * FROM `cmsb_my_table`) AS mt2 WHERE mt2.account = mt.account AND mt2.year = mt.year GROUP BY account, `year` )";mysqli()->query($deleteQuery) or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");

I tried many different versions of this query using back-ticks and quotes around tables and columns, but to no avail. I tried with and without a semi-colon at the end and that doesn't seem to matter. I'm not sure where this went wrong.

This is the error that I get:

MySQL Error: 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 &#039;mt WHERE recent_date NOT IN ( SELECT &#039; at line 1

I interpret this as saying there is an error on line 1 near 'mt WHERE recent_date NOT IN ( SELECT'. Can I not use aliases like I do with mt here? Does mt need to be in back-ticks? Something else that you might notice? Any help would be greatly appreciated.