CODEWITHSUNDEEP
mysqlsqlstored-procedures
rocky
rocky

Reputation: 435

deleting the data after copying it into another database

I have a stored procedure , its contents are as follows:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`MailMe`@`%` PROCEDURE `sp_archivev3`()
BEGIN

INSERT INTO 
     send.sgev3_archive(a_bi,
                        b_vc,
                        c_int,
                        d_int,
                        e_vc,
                        f_vc,
                        g_vc,
                        h_vc,
                        i_dt,
                        j_vc,
                        k_vc,
                        l_vc,
                        m_dt,
                        n_vch,
                        o_bit)
SELECT     a_bi,
           b_vc,
           c_int,
           d_int,
           e_vc,
           f_vc,
           g_vc,
           h_vc,
           i_dt,
           j_vc,
           k_vc,
           l_vc,
           m_dt,
           n_vch,
           o_bit

FROM   send.sgev3

WHERE m_dt BETWEEN  '2014-06-09' AND CURDATE();


END

Since, my query is inserting the records into send.sgev3_archive from send.sgev3. I want to do one more thing. I want to delete the records present in the send.sgev3 table after selecting and inserting the same into send.sgev3_archive. Should I write the DELETE query right below the SELECT query in my code above? Just wanted to confirm as I don't want to mess up my real data and accidently delete any records without getting it copied. Please advise.

Upvotes: 0

Views: 2162

Answers (1)

Rahul
Rahul

Reputation: 77896

Yes exactly. Include a DELETE statement saying

DELETE FROM   send.sgev3
WHERE m_dt BETWEEN  '2014-06-09' AND CURDATE();

To be more sure that INSERT does completes before DELETE invokes; wrap the INSERT and DELETE in a Transaction Block saying

START TRANSACTION;
INSERT INTO send.sgev3_archive ...
SELECT ... FROM send.sgev3
COMMIT;

You can as well handle error condition in your procedure and ROLLBACK the entire transaction by using exit handler in stored procedure. Below post already shows an way to do the same. Take a look.

How can I use transactions in my MySQL stored procedure?

MySQL Rollback in transaction

EDIT:

why transaction is necessary? Can't I just proceed like the way I have mentioned in my question?

Instead of explaining you why; let's show you an example (Quite resemble your scenario)

Let's say you have a table named parent declared as 

create table parent(id int not null auto_increment primary key,
`name` varchar(10),city varchar(10));

Insert some records to it

insert into parent(`name`,city) values('sfsdfd','sdfsdfdf'),('sfsdfd','sdfsdfdf'),('sfsdfd',null)

Now, you have another table named child defined as below (Notice the last column has not null constraint)

create table child(id int not null auto_increment primary key,
`name` varchar(10),city varchar(10) not null)

Now execute both the below statement (what you are currently doing)

insert into child(`name`,city) select * from parent;
delete from parent;

Result: INSERT will fail due to the not null constraint in child table but delete will succeed.

To avoid this exact scenario you need Transaction in place. so that, if INSERT fails you don't go for delete at all.

A pseudo code on how you handle this in transaction

start transaction

insert into child(`name`,city) select * from parent;

if(ERROR)
rollback
exit from stored proc

else
commit
delete from parent;

SideNote: exit from stored proc can be implemented using LEAVE

Upvotes: 3

Related Questions