MariaDB – Troubleshooting Subquery Error 1064 When Used in an ‘IN’ Clause

mariadbsubquery

This must be a very simple problem, the subquery returns results alone but the compound query reports:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table where table in (select table_name from information_schema.tables where tab' at line 1

Here's the query:

select * 
from table 
where table in 
    (select table_name 
     from information_schema.tables 
     where table_schema='my_database' 
     limit 1);

I have also tried it without the limit, that was my first attempt:

select * 
from table 
where table in 
    (select table_name 
     from information_schema.tables 
     where table_schema='my_database');

Best Answer

You can't SELECT like this, where you dynamically SELECT the table name. That's why it's not working. The subselect works, but.. try to replace subselect by a constant (i.e. known) name and it still won't work.

For something like this you either need an if then else flow or dynamic SQL or do it on application side.

Related Solutions

Mysql – ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB

Are you sure you still have an error with the following script ?

change :

CREATE TABLE IF NOT EXISTS `mydb`.`Order_has_Product` (
  `Order_id` INT{+ NOT+} NULL,
  `Product_product_id` INT{+ NOT+} NULL,

(it work on my MySQL 5.7, I don't have any mariadb to try now)

-- MySQL Script generated by MySQL Workbench
-- Thu 12 Oct 2017 01:11:28 PM EDT
-- Model: New Model    Version: 1.0    
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
-- -----------------------------------------------------
-- Schema customer
-- -----------------------------------------------------
-- This schema was created for a stub table

-- -----------------------------------------------------
-- Schema customer
--
-- This schema was created for a stub table
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `customer` ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Product` (
  `product_id` INT NOT NULL AUTO_INCREMENT,
  `Product_name` VARCHAR(45) NULL,
  `color` VARCHAR(45) NULL,
  `cost` VARCHAR(45) NULL,
  `price` INT NULL,
  `catagory` VARCHAR(45) NULL,
  `description` VARCHAR(45) NULL,
  `stock` VARCHAR(3) NULL,
  PRIMARY KEY (`product_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
  `name` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `country` VARCHAR(45) NULL,
  `customer_id` VARCHAR(45) NOT NULL,
  `state` VARCHAR(2) NULL,
  `zip` INT NULL,
  `credit_card` BIGINT(17) NULL,
  PRIMARY KEY (`customer_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `price` INT NULL,
  `date` DATE NULL,
  `shipping_date` DATE NULL,
  `completed` VARCHAR(3) NULL,
  `urgent_order` VARCHAR(3) NULL,
  `vip` VARCHAR(3) NULL,
  `quantity` INT NULL,
  `product_id` INT NULL,
  `repeat_order` VARCHAR(3) NULL,
  `customer_customer_id` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Order_customer1_idx` (`customer_customer_id` ASC),
  CONSTRAINT `fk_Order_customer1`
    FOREIGN KEY (`customer_customer_id`)
    REFERENCES `mydb`.`customer` (`customer_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`review`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`review` (
  `review_id` INT NOT NULL AUTO_INCREMENT,
  `rating` INT NULL,
  `review` VARCHAR(45) NULL,
  `order_id` INT NULL,
  `date` DATE NULL,
  `Product_product_id` INT NULL,
  PRIMARY KEY (`review_id`),
  INDEX `fk_review_Product1_idx` (`Product_product_id` ASC),
  CONSTRAINT `fk_review_Product1`
    FOREIGN KEY (`Product_product_id`)
    REFERENCES `mydb`.`Product` (`product_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Order_has_Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Order_has_Product` (
  `Order_id` INT NOT NULL,
  `Product_product_id` INT NOT NULL,
  PRIMARY KEY (`Order_id`, `Product_product_id`),
  INDEX `fk_Order_has_Product_Product1_idx` (`Product_product_id` ASC),
  INDEX `fk_Order_has_Product_Order1_idx` (`Order_id` ASC),
  CONSTRAINT `fk_Order_has_Product_Order1`
    FOREIGN KEY (`Order_id`)
    REFERENCES `mydb`.`Order` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Order_has_Product_Product1`
    FOREIGN KEY (`Product_product_id`)
    REFERENCES `mydb`.`Product` (`product_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

USE `customer` ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Mysql – Trying to run query and getting error: Error 1064 (42000)

select 
       flwusertest_users.ID, 
       flwusertest_users.display_name, 
       flwusertest_users.user_email 
from 
      flwusertest_users  
INNER JOIN 
      flwusertest_usermeta 
      ON flwusertest_users.ID = flwusertest_usermeta.user_id 
WHERE (
      flwusertest_usermeta.meta_key = '_um_last_login' 
      AND flwusertest_usermeta.meta_value NOT BETWEEN '1483228800' and '1520952132'
      ) 
AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID)
                         FROM   
                                flwusertest_users 
                         INNER JOIN 
                                flwusertest_em_bookings 
                               ON flwusertest_users.ID=flwusertest_em_bookings.person_id 
                        WHERE 
                              flwusertest_em_bookings.booking_status=1);

There is more than one error in your sentence.

AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID),
                                flwusertest_users.display_name,
                                flwusertest_users.user_email

You cannot return more than one field to use it with IN. You should return only

AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID)
                         FROM   
                                flwusertest_users 
                         INNER JOIN 
                                flwusertest_em_bookings 
                               ON ...