Question 21870 in Mysql Question posted in
The official MySQL documentation can be found here.

Need to retrieve orders from database based on below condition – Mysql

I am writing a Flexible Search query to get the orders from database and delete them. The condition to get the orders is that : if the order is placed 3 months before the current date, I need those orders to be deleted from db.
Example: currentDate: 12/07/2022; orderCreationDate: 01/06/2022. As this orderCreationDate is 3 months older than currentDate, I need to fetch this order.
Can you please help me modify this query accordingly?

select {code},{creationtime},{date} from {order} order by {date}
Tags:

2

Answers


  1. 0

    We can use DATE_SUB:

    DELETE FROM orders
WHERE orderDate <= 
  DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

    See the documentation

    Try out: db<>fiddle

    Note: If you want to do today’s good deed, you should rename your table name "order" and your column name "date" (as I did in my command).

    It’s recommended to avoid using SQL key words as table names or column names if possible.

    Login or Signup to reply.
  2. 0

    Using the OUTPUT command you will capture the rows you are deleting.

    Like this:

    USE [tempdb]

IF OBJECT_ID('Orders') IS NOT NULL DROP TABLE Orders
CREATE TABLE Orders (ID int, orderCreationDate datetime)
INSERT INTO Orders values (1, GETDATE()), (2, DATEADD(MONTH, -1, GETDATE())), (3, DATEADD(MONTH, -2, GETDATE())), (4, DATEADD(MONTH, -3, GETDATE())), (5, DATEADD(MONTH, -4, GETDATE()))

SELECT * FROM Orders

IF OBJECT_ID('OrdersDeleted') IS NOT NULL DROP TABLE OrdersDeleted
CREATE TABLE OrdersDeleted (ID int, orderCreationDate datetime)

DELETE Orders OUTPUT deleted.* INTO OrdersDeleted WHERE DATEADD(MONTH, 3, orderCreationDate) > GETDATE() 

SELECT * FROM OrdersDeleted
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search