Menu
There are a lot of questions on how to delete all rows older than 30 days but i can't find anything same with mine so i can fix it
i need to delete some records of messages that are older than 30 days, the column name with the date is named sentOn and the rows in that column looks like this 2018-01-12 12:25:00 How should i format my query to delete all records from the table containing those that are older than 30 days?
DELETE FROM messages WHERE sentOn < '2018-02-21 00:00:00';
would this work?
EDIT: above query works but very very slowly any way to make it faster? i tried now() but it gives error that the function is wrong
446
There are a few ways to delete multiple rows in a table. If you wanted to delete a number of rows within a range, you can use the AND operator with the BETWEEN operator. DELETE FROM table_name WHERE column_name BETWEEN value 1 AND value 2; Another way to delete multiple rows is to use the IN operator.
How do I find last 30 days in SQL? SELECT * FROM product WHERE pdate >= DATEADD(day, -30, getdate()).
You cannot use ORDER BY or LIMIT in a multiple-table DELETE . The table_references clause lists the tables involved in the join, as described in Section 13.2. 10.2, “JOIN Clause”. For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted.
The following code will delete the records of messages that are older than 30 days
DELETE FROM messages WHERE sentOn < NOW() - INTERVAL 30 DAY;
The NOW() method in MySQL is used to pick the current date with time. INTERVAL 30 DAY used for subtracting 30 days from the current date.
After the above query, you can check the current table using the SELECT statement. Thank you!
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
