How do I over-write one table with another?

[jeddi]

Hi,

I do a daily update to my table of about 20,000 records from
an xlm file. this takes about 4 - 5 minutes.

The first thing I do is TRUNCATE the "current" table and then build the new one.

This means that during those 4-5 minutes, no data is available to users.

Rather than working on the "current" table, I am thinking of using a
temporary table to build to and then copy it on top of the "current" table.

I think the copy over ( or over-writing) will be a quicker action than the build.

I am not sure how I should write the query to copy the
"temporary" to the "current" table so that it over-writes all the records.

And while doing the over-write is access blocked to my users ?
(If it is, maybe it is only on a row basis, so would'nt effect anyone ?? )

Would appreciate any help.

Thanks.


.

[koko5]

Hi,
You can use replace instead of insert.

REPLACE INTO T1 (SELECT * FROM T2);

Table locking depends on engine and transactions (if you use). For MyISAM you should not have troubles if you don't call lock tables implicitly.
Regards,
Nick

[jeddi]

Thanks Nick :thumbsup:

[koko5]

Welcome :)

There is situation where the above will not be correct.
Let's say that (new) t2 has 1record and t1-2recs.
Than we've to use the next two queries:

REPLACE INTO t1 (SELECT * FROM t2);
DELETE t1 FROM t1 NATURAL LEFT JOIN t2 WHERE t2.id IS NULL;

^^Deleting records from t1 which do not exists in (new) t2 table (to prevent truncating t1 first).
Hope it helps.
Regards,
Nick