Pangolin
Pangolin

Reputation: 7444

Move record to different table MySQL

I see this question have been asked in many different ways, but I don't understand them quite well.

I want to move my records that have expired (their date is behind/smaller than now() - each one has a field with their date in, this is in seconds from 1970) to a different identical table. This is to make the event-cue smaller in the main table.

Is there any single query I can use to move a record to a different table? I can do the checking of the time/when expires myself.

Upvotes: 1

Views: 927

Answers (2)

Mircea Soaica
Mircea Soaica

Reputation: 2817

insert into backupTable (select field1, field2 from mainTable where myDate < UNIX_TIMESTAMP(NOW()))

delete from mainTable where myDate < UNIX_TIMESTAMP(NOW())

Upvotes: 0

cichy
cichy

Reputation: 10644

You cant do this in one query, you have to perform two: query 1:

INSERT INTO newtable SELECT * FROM oldtable WHERE date<now();

query 2:

DELETE FROM oldtable WHERE date <= SELECT MAX(date) FROM newtable

Upvotes: 5

Related Questions