CODEWITHSUNDEEP
mysqlstored-procedures
Murcielago
Murcielago

Reputation: 1005

MySQL procedure to update a price based on a date change

I am trying to write a procedure that update the the content in one column, based a change in date is more than 20 days. In context, the procedure decreases the price of a good if the time passed since acquired is more than 20 days.

I am using the DATEDIFF function as well as GETDATE() statement but I am really strugling. At this point I am stucked.

Can anybody tell what is wrong with my sql code? 

DELIMITER //

CREATE PROCEDURE UPDATE_PRICE 

DECLARE @TIMEPASSED AS TIME
SET @TIMEPASSED = GETDATE()

BEGIN 
UPDATE sales SET  sales.SalesPrice = sales.SalesPrice - 30 
WHERE DATEDIFF(@GETDATE(), Sales.AcquisitionDate;

END;;

Any help on this one would be much appreciated

Upvotes: 1

Views: 340

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Variable declarations should be in the BEGIN/END block, but they are not really needed in this case. GETDATE() is not a MySQL function. So:

DELIMITER //

CREATE PROCEDURE UPDATE_PRICE 
BEGIN 
    UPDATE sales s
        SET s.SalesPrice = s.SalesPrice - 30 
        WHERE s.AcquisitionDate < CURDATE() - INTERVAL 20 day;
END;

DELIMITER ;

Upvotes: 1

zip
zip

Reputation: 4061

Try this:

DELIMITER //

CREATE PROCEDURE UPDATE_PRICE 

DECLARE @TIMEPASSED AS TIME
SET @TIMEPASSED = GETDATE()

BEGIN 
UPDATE sales SET  sales.SalesPrice = sales.SalesPrice - 30 
WHERE DATEDIFF(@GETDATE(), Sales.AcquisitionDate) > 20;

END;

Upvotes: 0

Related Questions