I want to delete the records between two dates

Hello Friends,

I want to delete the records between two dates and also before deleting those records, insert the sane into another table for backup...

Table Name
-----------
1) TableA

2) TableA_Backup

That is delete the records and also store a backup in other table... like in our email system.. once we delete any mail it will be stored in trash


date is in the format 2009-06-26

with regards,
seyed

 

I would use a trigger in combination with a delete query.

This is assuming that the two tables are identical.

Here's the trigger.

CREATE TRIGGER make_backup
BEFORE delete ON TableA
FOR EACH ROW
BEGIN

INSERT INTO TableA_Backup SELECT OLD.*

END;

Once this is added to the table, every time a record is deleted it will be automatically copied to the other table. You wont need to run any other query or loop or anything else.

For the delete, assuming that the column type is date.

DELETE FROM TableA WHERE date_column <= 'YYYY-MM-DD';

or between dates...

DELETE FROM TableA WHERE date_column > 'YYYY-MM-DD' AND date_column <= 'YYYY-MM-DD';

These are both untested but should get you on the right track.

 

the trigger is not working mate...

 

Also i have one more problem...

I want to transport local machine database records to the remote database... If the same value already exist means the row should be updated else insert

 

I'm not sure how to do a trigger but you could just retrieve those records then loop through them and insert them into the backup table, something like:

//Query to find records between two dates
$query = "SELECT * FROM TableA WHERE date => 'yyyy-mm-dd' AND <= 'yyyy-mm-dd' ";
//Run the query and store them
$result = mysql_query($query) or die(mysql_error());
//Loop through them
while($row = mysql_fetch_array($result)){
   //Insert the Record into TableA_backup
}

//Now delete these rows as they have been backed up
$deleteQuery = "DELETE FROM TableA WHERE date => 'yyyy-mm-dd' AND <= 'yyyy-mm-dd' ";
mysql_query($deleteQuery) or die(mysql_error());

PHP:

It's a bit long-winded so if your going to use it regulary you might want to look at other solutions e.g the trigger solution.

As for your second problem you could just do the same as i've done above, so store all the local database in a query like:

//Store all local machine database records
$result = mysql_query("SELECT * FROM local_machine_database_table");
//Loop through eahc of these records one at a time
while ($row = mysql_fetch_array($result)){
//Now check if the value in this record matches any in the remote database table
$result = mysql_query("SELECT * FROM remote_database_table WHERE value = $row['value']");
//If statement to check if any records were retrieved
if($result) {
//The value does exist so update it
UPDATE VALUE etc..
}
else {
//The value doesn't exist so insert it
INSERT VALUE etc..
}
}

PHP:

Again there might be a better solution but its an option

 

@wd_2k6

I used your first code.. when i execute it, only the first record is inserting, the rest are not inserting but deleted.

 

They should all be inserting, but I have made an error where greater than should be ">=" not "=>" sorry.

If the delete is working then the query must be storing all records in $result correctly, and all you are doing is looping through each one one at a time, so just make sure you are inserting it correctly inside the while statement.

The insert statement would be like

$insert = "INSERT INTO TableA_Backup VALUES (' ', 'values', 'etc')";
$mysql_query($insert) or die(mysql_error());

PHP:

So if there is a problem inserting one an error will be reported.