SQL Question

Manat

Hi all,

I have a MySQL table with many entries in there.  The timestamp field in in this format: 20040301072809

I would like to take all fields wherein the timestamps dates back to 2002 and put that in a seperate table and delete it from the current one so that it only has 2003 and 2004.

Can anyone help me with the SQL needed for this?

Thx
MK

nemonoman

See this thread for archiving advice.

To select the records you need in your case is simpler than the:

SELECT *
FROM yourOriginalTable
WHERE timestamp LIKE '2002%'

will find all rows with timestamps starting in year 2002

rweston002

Originally posted by nemonoman
See this thread for archiving advice.

To select the records you need in your case is simpler than the:

SELECT *
FROM yourOriginalTable
WHERE timestamp LIKE '2002%'

will find all rows with timestamps starting in year 2002

use SELECT INTO (Documentation here > w3schools )

SELECT * INTO new_table
FROM old_table WHERE timestamp LIKE '2002%' <- i use the Where clause from above... not sure if that will work..

Then:

DELETE FROM old_table WHERE timestamp LIKE '2002%'

Manat

I did this

SELECT * INTO tracking02 FROM Tracking WHERE TrackingDate LIKE '2002%'

And I get an SQL error. TrackingDate is a timestamp data type.

Any idea why?

nemonoman

If you are using MySQL, you got an error because there is no SELECT * INTO statement that creates backup tables.

Did you bother to look at the link I provided?

INSERT INTO backupTable (YourColumn1, YourColumn2...etc.)
SELECT NewsColumn1, NewsColumn2, etc FROM originalTable WHERE timestampColumn like '2002%'

Then
DELETE FROM originalTable

WHERE timestampColumn like '2002%'

You need to change YourColumn1, etc to the names you are using.