DELETE entry that is older than current datetime in MySQL

Question

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

which RDBMS are you using? MySQL,SQL Server,postgresql,..... . tag it correctly — RF1991, Apr 17 '22 at 04:50
Try `quoting` your column name with backticks -- eg DELETE FROM table_name WHERE ``from`` < '2022-04-16 08:00:00' — hd1, Apr 17 '22 at 04:51
Does this answer your question? [Syntax error due to using a reserved word as a table or column name in MySQL](https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — Nigel Ren, Apr 17 '22 at 06:36

score 5 · Accepted Answer · answered Apr 17 '22 at 04:53

5

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

answered Apr 17 '22 at 04:53

Tim Biegeleisen

502,043
27
286
360

I think SQL will be able to "get it", that second from is not a keyword. OR it would have given an error? – Akash Varde Apr 17 '22 at 04:55
`FROM` is not just a keyword, but a *reserved* keyword. – Qirel Apr 17 '22 at 04:56
@AkashVarde For certain, the OP's query will not run without error. There could be other problems as well though. – Tim Biegeleisen Apr 17 '22 at 04:57
@TimBiegeleise backticks did the trick. Thanks :) – python19 Apr 17 '22 at 05:35
Isn't this just a duplicate of https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql – Nigel Ren Apr 17 '22 at 06:37

score 1 · Answer 2 · answered Apr 17 '22 at 04:55

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?

It's really not difficult to know what NOW() will return. – Jonas Metzler Apr 17 '22 at 04:59 — Jonas Metzler, Apr 17 '22 at 04:59

score 1 · Answer 3 · answered Apr 17 '22 at 05:02

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.

DELETE entry that is older than current datetime in MySQL

3 Answers3