MySQL error 1064, not sure what I'm doing wrong here...

Question

mysql -u username -p database -e deletedata.sql

I get ERROR 1064 (42000) at line 1: 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 'deletedata.sql' at line 1

DELETE FROM 'table1' WHERE 'column' <= date_sub(current_date, INTERVAL 37 day);
DELETE FROM 'table2' WHERE 'column' <= date_sub(current_date, INTERVAL 37 day);

What could I be missing?

Answer 1

Single quotes in MySQL denote string constants. You should be using back quotes (`) to escape table / column names (or identifiers in general).

Answer 2

I imagine you're probably getting into trouble with using table identifiers in the FROM clause. I stole some examples from one of my databases. So this will fail:

DELETE FROM Session s WHERE s.startTime<=date_sub(NOW(),INTERVAL 30 day);

Which is really strange because just replacing "DELETE" with "SELECT *" works.

But this will work fine:

DELETE FROM Session WHERE startTime<=date_sub(NOW(),INTERVAL 30 day);
DELETE s FROM Session s WHERE s.startTime<=date_sub(NOW(),INTERVAL 30 day);

Syntactic oddities. Fun! :-D

Answer 3

-e executes a statement, not a sql file. What you need:

mysql -u user -p pass db

and then into the mysql cli:

\. deletedata.sql

Backslash dot means "source file"

Or you can just pipe it:

cat delete.sql | mysql -u user -p pass db