python19
python19

Reputation: 21

DELETE entry that is older than current datetime in MySQL

I have tried the following among various other statements but thought one of these should obviously work but no luck so far. Please tell me what I'm doing wrong. Not getting an error, it's just not working.

DELETE FROM table_name WHERE from < NOW()
DELETE FROM table_name WHERE from < '2022-04-16 08:00:00'

Example

Upvotes: 1

Views: 857

Answers (3)

Matthias Radde
Matthias Radde

Reputation: 361

As from is a reserved word you have to escape its name. Or better change the column-name to something that is not a reserved keyword.

Within MySQL you can do with backticks or (if you MySQL-server is running in ANSI-mode - see https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html) with double-quotes

DELETE FROM table_name WHERE `from` < NOW();
DELETE FROM table_name WHERE "from" < NOW();

The double-quotes are also working with PostgreSQL (so it is more standard-SQL) whereas the backticks are only used with MySQL.

Upvotes: 1

Sohan Arafat
Sohan Arafat

Reputation: 93

You have done many problems here. First of all 'from' is a reserved keyword. So please change the word into something to avoid future problems. And please convert those into timestamp. What you are doing is comparing it with a string eg.'2022-04-16 08:00:00' . And you have to know what NOW() is returning. If you convert everything in timestamp you will get an integer to compare with. Or use date related functions. Another thing what is the data type of this column?

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You shouldn't name your columns (or other database objects) using reserved SQL keywords such as FROM. That being said, both of your queries are valid once we escape the from column:

DELETE FROM table_name WHERE `from` < NOW();
DELETE FROM table_name WHERE `from` < '2022-04-16 08:00:00';

Upvotes: 5

Related Questions