CODEWITHSUNDEEP
mysql
tonymx227
tonymx227

Reputation: 5451

MySQL procedure to insert and delete

I'd like to create an MySQL procedure to insert in a table and remove in an other table...

My procedure :

insert ignore into old_events (select * from events where start < now());
delete from events where start < now();

Error message : insert ignore into old_events (select * from events where start < now()); delete from events where start < now();

Upvotes: 1

Views: 85

Answers (2)

Vipin Jain
Vipin Jain

Reputation: 3756

As i saw you insert query you insert data into old_events column but you select all column from the events table. so you can select only that column which you want to insert into old_events column

INSERT IGNORE INTO old_events 
SELECT * FROM events 
WHERE start < now();

DELETE FROM events WHERE start < now();

In my System i have created two table 

create table events( id int,event_name varchar(20), start timestamp default current_timestamp);
create table old_events( id int,event_name varchar(20), start timestamp default current_timestamp);

and then 6 rows inserted into events table 

insert into events(id,event_name, start) values 
(1,'event1','2016-02-18'),
(2,'event2','2016-02-19'),
(3,'event3','2016-02-20'),
(4,'event4','2016-02-21'),
(5,'event5','2016-02-22'),
(6,'event6','2016-02-23');

and i execute select query where start is less than current time

SELECT * FROM events 
WHERE start < now();

its return 5 rows see example

but in sqlfiddle, we can run only select query so after this execute below query in my system and it successfully execute and 5 rows inserted into old_events where start is less than current time

INSERT INTO old_events
SELECT * 
FROM EVENTS WHERE START < NOW();

after than i select rows from old_events table its return 5 rows

select * from old_events;

after than i execute delete query and its delete 5 rows from events table

DELETE FROM EVENTS WHERE START < NOW();

after all step i execute two more query select from events and old_events table

select * from events;  // its return 1 row where start is greater than now()
select * from old_events; // its return 5 row where start is less than now()

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175954

Remove () when using INSERT INTO SELECT:

insert ignore into old_events 
select * 
from `events` 
where `start` < now();

Consider using column list instead of *:

insert ignore into old_events(col1, col2, ...) 
select col1, col2, ... 
from `events` 
where `start` < now();

Keep in mind that now() is time-dependent and you should store its result in variable for comparing. Between INSERT and DELETE now() could change.

Upvotes: 1

Related Questions