Sign up |
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 (
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 'mt WHERE recent_date NOT IN ( SELECT ' 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.
I figured out the problem. When using aliases for tables with a delete, you have to specify the aliased table for deletion. So my code before should have started like this:
DELETE mt FROM
Thanks for the plugin, that looks to be useful!