SQL Query Help

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

By (Deleted User) - 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 Dave - March 5, 2018

Hi testplan, 

I don't see anything immediately obvious about why that query wouldn't be working.

I'd try executing the subqueries individually and then adding and removing lines to isolate the exact issue.

For example, does this run on it's own: 

                  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`

And if so, does the containing query run if you replace the sub-query with the output of the subquery?

Also, if you don't already have it, this free plugin lets you run MySQL queries straight from the CMSB plugin menu:
https://www.interactivetools.com/add-ons/developer-console/

Hope that helps point you in the right direction.

Dave Edis - Senior Developer

interactivetools.com

By (Deleted User) - 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:

DELETE mt FROM

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

interactivetools.com