deleting the data after copying it into another database

Question

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.

Especially if `m_dt` is really a timestamp, you shouldn't be using [the inclusive upper-bound of `BETWEEN`](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). You also need to use the _same value_ for the upper bound in both statements, so you need to get that separately, first. For that matter, you may wish to be able to pass a range to your stored procedure, not just use a predefined range. — Clockwork-Muse, Jul 13 '14 at 00:02

score 3 · Accepted Answer · edited May 23 '17 at 12:15

3

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

edited May 23 '17 at 12:15

Community

1
1

answered Jul 11 '14 at 23:41

Rahul

76,197
13
71
125

Okay, I will include `DELETE a_bi,b_vc.... `etc after the `SELECT` statement. By the way, on more question, assuming that the stored procedure is gonna run the statements sequentially, it should be running the `INSERT` `STATEMENT` first then `SELECT` and then `DELETE`. Shouldn't it be `SELECT` first and then `INSERT` and then `DELETE`? – rocky Jul 11 '14 at 23:48
You got it right. It will select the rows and then insert them in `insert into select from`. – Rahul Jul 11 '14 at 23:50
But I am doing opposite in the above query and it is still working as of now without deleting any data(which is obvious as I don't have DELETE command over there). I am wondering why? To be more clear, why my above query is going to run SELECT command first and then Insert it as SELECT command is mentioned below INSERT. – rocky Jul 11 '14 at 23:54
NO you are not. Read MySQL doc about `insert into select from` to get it clarified.BTW, No Offense but I recall, giving to answer to one of your earlier MySQL event related question; on which you didn't provided any feedback. Just a suggestion: don't do that. If you feel the answer not meeting what you are looking then say so otherwise accept the answer to show your appreciation. – Rahul Jul 11 '14 at 23:56
What is the point of using a separate transaction for each statement? – Andriy M Jul 12 '14 at 23:18
@AndriyM, that's an example to let OP know that he can use `trans` to make sure that `insert` gets successful and so pointed him to other post where it's been shown how to handle transaction in stored proc and rollback if needed. – Rahul Jul 12 '14 at 23:21
@rocky: What you've got in your stored procedure is not an INSERT statement *and* a SELECT statement. It's a single INSERT statement, more specifically a variation often referred to as INSERT...SELECT for brevity. (Just think about it: if you remove the SELECT part, what will remain? Just `INSERT INTO tablename (column list)`? That would make no sense, wouldn't it. Therefore, `SELECT ...` in that context is an integral part of the `INSERT` statement.) – Andriy M Jul 12 '14 at 23:22
Well, you are showing the syntax but the specific example just makes no sense. That might actually confuse them to a greater extent than educate. The point of declaring an explicit transaction is to make several distinct operations a single atomic operation, in the sense that if one fails, everything is rolled back. In contrast, what you've suggested will allow deletion even if the insert fails. – Andriy M Jul 12 '14 at 23:24
Not only would you need to include the `DELETE` in the same transaction, you also need to make sure that `CURDATE()` returns the same value each time; that is, you have to capture it separately, first, then use it in the statements. – Clockwork-Muse Jul 13 '14 at 00:05
Guys, Thanks for your help. May I know why transaction is necessary? Can't I just proceed like the way I have mentioned in my question? – rocky Jul 13 '14 at 06:40
@Rahul Why didn't you include `DELETE` in your edited answer of transaction? I mean just before `COMMIT`. Just wondering if there's any reason. – rocky Jul 13 '14 at 06:42
@rocky, You may want to check the edited answer (mainly the `EDIT:` part) to address your question `why transaction is necessary?` – Rahul Jul 15 '14 at 19:37

deleting the data after copying it into another database

1 Answers1