'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
mysqlsqlsql-deletedelete-row


Solution 1:[1]

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

Solution 2:[2]

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?

Solution 3:[3]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Sohan Arafat
Solution 3 Matthias Radde

Related Questions