MySql stored procedure to update the database record

Question

I hasn't been writing any MySql stored procedures before, so I don't know them at all. I have one database table which has some records in it. The main column is dateTime - when this record was saved in the database.

I have wrote the MySql stored procedure to select every record from the specified date:

CREATE PROCEDURE getMessages(IN dateFrom DATETIME)   
    SELECT * FROM EsbMessage WHERE dateTime <= dateFrom;

And this is the call:

CALL getMessages('2012-10-04 13:11:09');

This works correctly, it returns me the records from the specified date.

What I need to do is:

If the record is over one week old, I need to update the other column.
If the record is over one year old, I need to delete that record.

I can easily do this programmatically, but in this case I have to do this using stored procedure.

So I'am thinking of something like this:

CREATE PROCEDURE updateMessages(IN dateFrom DATETIME)   
    BEGIN
      SELECT * FROM EsbMessage WHERE dateTime <= dateFrom;
      #for each message
      #if the message is over one week old but not over one year old:
      UPDATE EsbMessage SET body = '';
      #if message is over one year old:
      DELETE FROM EsbMessage WHERE dateTime = #message.dateTime
END

But I don't know how to use for loop in stored procedure, and how to write if statements depending on my requirements and the other thing I don't now how to count the dates in MySql. For e.g. If I have the current date then I need to subtract the 365 days from the current date.

Could somebody help me with this issue?

martinwnet · Accepted Answer · 2012-10-04 14:09:19Z

1

You wouldn't need a loop, just have your conditions in the WHERE clause:

#if the message is over one week old but not over one year old:
UPDATE EsbMessage SET body = ''
WHERE dateTime >= DATE_SUB(NOW(),INTERVAL 1 WEEK) AND dateTime <= DATE_SUB(NOW(),INTERVAL 1 YEAR);

#if message is over one year old:
DELETE FROM EsbMessage WHERE dateTime >= DATE_SUB(NOW(),INTERVAL 1 YEAR);

edited Oct 4, 2012 at 14:09

answered Oct 4, 2012 at 12:53

martinwnet

5812 gold badges4 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hache · Accepted Answer · 2012-10-04 12:53:58Z

0

How to loop and to use if clauses is described here: http://www.mysqltutorial.org/stored-procedures-loop.aspx

I would do it without loops:

CREATE PROCEDURE updateMessages(IN dateFrom DATETIME)   
BEGIN      
  UPDATE EsbMessage SET body = '' where dateTime <= dateFrom -(86400*7); //86400 = 1 day
  #if message is over one year old:
  DELETE FROM EsbMessage where dateTime <= dateFrom -(86400*365);
END

answered Oct 4, 2012 at 12:53

Hache

4493 silver badges7 bronze badges

1 Comment

Roman Newaza Over a year ago

How about leap seconds and years? :-)

Collectives™ on Stack Overflow

MySql stored procedure to update the database record

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related