Home > Mobile >  DELETE entry that is older than current datetime in MySQL
DELETE entry that is older than current datetime in MySQL

Time:04-17

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

CodePudding user response:

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

CodePudding user response:

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?

CodePudding user response:

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.

  • Related