CODEWITHSUNDEEP
mysqlsqlstored-procedures
Paulius Matulionis
Paulius Matulionis

Reputation: 23415

MySql stored procedure to update the database record

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:

  1. If the record is over one week old, I need to update the other column.
  2. 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?

Upvotes: 1

Views: 5795

Answers (2)

martinwnet
martinwnet

Reputation: 581

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

Upvotes: 1

Hache
Hache

Reputation: 449

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

Upvotes: 0

Related Questions