0

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');

2 Answers 2

1

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.

1

table is a reserved word, so it is not considered a an identifier, but part of the syntaxis. If you have a table table, and a column named table (confusing), you must refer to them with inverted quotes like this: `table`.

Try to avoid reserved words on table and column identifiers, e.g. my_table or table_name.

1
  • thank you, I knew I was doing something dumb. I still can't run the query but Vlad explained why so I accepted his answer. Mar 19, 2021 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.