4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 6, 2018   (RSS)

By testplan - March 3, 2018

Hello all,

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 '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. 

By testplan - March 6, 2018

Thanks Dave,

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:


Thanks for the plugin, that looks to be useful!

By Dave - March 6, 2018

Ok, great.  Glad to hear you got it working! Thanks for sharing the resolution.  Cheers.

Dave Edis - Senior Developer