How to move records older than 30 days to another table in MYSQL?

Question

I have a table called activation. In here I want to move records older than 30 days to another table. How to do this?

 SELECT * DATE_FORMAT(datetime, '%m/%d/%Y') 
 FROM 
 tablename 
 WHERE datetime <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)

I tried this query but it returns me an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_FORMAT(datetime, '%m/%d/%Y') FROM activation WHERE datetime <= DATE_SUB(SYS' at line 1

you are missing comma (`,`) between `*` and `DATE_FORMAT(datetime, '%m/%d/%Y')`, it should be `SELECT * ,DATE_FORMAT(datetime, '%m/%d/%Y') FROM .....` — kamal pal, Jun 01 '16 at 12:09
no, that's the format you want date to be appear at last column, take a look a date_format function, https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format — kamal pal, Jun 01 '16 at 12:23
Okay thanks that solved my error but could you guide me how to move those records to another table — Rajan, Jun 01 '16 at 12:26
if you already have table created, simply do `insert into newtable select columnnames from oldtable`, as stated in answers — kamal pal, Jun 01 '16 at 12:35

score 3 · Accepted Answer · edited Jun 01 '16 at 12:35

3

You can try like

INSERT INTO tablename2 select *, DATE_FORMAT(datetime, '%m/%d/%Y') from tablename1 WHERE datetime <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY);

DELETE FROM tablename1 WHERE datetime <= DATE_SUB(SYSDATE(), INTERVAL 30 DAY);

edited Jun 01 '16 at 12:35

Ben Swinburne

25,669
10
69
108

answered Jun 01 '16 at 12:31

Dr Manish Lataa-Manohar Joshi

2,380
5
17
38

i get this error :1136 - Column count doesn't match value count at row 1 – Rajan Jun 01 '16 at 12:34
i am still getting that error why? – Rajan Jun 01 '16 at 12:38
1

plz check if column count in table1 and table 2 are same ? – Dr Manish Lataa-Manohar Joshi Jun 01 '16 at 12:49
@Ben Swinburne Thanx for edit – Dr Manish Lataa-Manohar Joshi Jun 01 '16 at 12:50

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

Example to how implement:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2)
SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM tablename GROUP BY LongIntColumn1;

From: https://stackoverflow.com/a/74204/2925795

edited May 23 '17 at 12:15

Community

1
1

answered Jun 01 '16 at 12:28

user2925795

400
10
29

How to move records older than 30 days to another table in MYSQL?

2 Answers2