[SOLVED] table update philosophy

[AV1611]

I have a lot of tables that I update en mass via cron.

I need to delete the table then parse in new data from text file. 

no problem

Philosophy question:

I don't want to delete the old table until I'm sure that the new data is valid.

Is the right method to parse the new data into an array then test the array then delete the old table then parse in the new data from that array, or is there a better way?

Perhaps write to temp table first?

this is a philosophy question

[JasonLewis]

i would think it would depend on how your testing it, and also what you are more experienced with. i would probably use arrays because i find they are easier to deal with. :)

[corbin]

You could insert the data into table_new then verify the data by counting the rows or something and then drop table if its valid then rename table_new table.

[MCP]

From a DB theory point of view, I believe the preferred method would be roughly:

-----
start transaction
delete old table
insert new rows into the table
if error then rollback transaction
else commit transaction
-----

[corbin]

There has to be  backup to do that htough which makes another step

[JasonLewis]

you wouldnt want to delete the old table straight off incase there is something wrong. i am starting to think that inserting the new table as a test name, checking it then if it is good delete the old table then rename the new table, which i think was mentioned. it just sounds easierish :)

[MCP]

[quote author=corbin link=topic=120607.msg494976#msg494976 date=1167700661]
There has to be  backup to do that htough which makes another step
[/quote]

backup? I don't think so... The database will handle everything without any sort of "backup". Whatever it does behind the scenes is not important -- there's no additional steps from a user point of view.


ProjectFear -- what sort of tests are you performing on your data?

[AV1611]

So, what I think I am hearing is this:

Pull the new data to a temporary table
Test for appropriate rows in the table
delete the contents of the old table
update the old table with the contents of the temporary table

That seems safe to me...

Agreed?

[JasonLewis]

yeh i'll agree on that. seems the best bet to me.