I have a database with frequently changing raw data.
There is a pretty complex process that creates a summary table of this data.
I've created a Stored Procedure to create this table which uses a sequence of three tables. As I don't yet track what data has been updated (I'm working on that) I simply populate these queries with a sequence of:
TRUNCATE tablename ...
INSERT INTO tablename ...
START TRANSACTION READ WRITE;
[Sequence of TRUNCATE / INSERT queries]
NO!! , you cant use TRUNCATE in a transaction. Truncate deletes the hole Table File an re-creates it . Truncate operations cause an implicit commit.
It can be work with RENAME. Rename is a single transaction
CREATE TABLE table_copy like your_table; INSERT DATA in table_copy; RENAME your_table TO your_table_old, table_copy TO your_table; DROP table_copy;