I have two tables with identical structures:
Table1 and Table2
id INT PRIMARY KEY, a TINYINT, b TINYINT, c TINYINT
Table1 is actively being used by queries from other sessions; Table2 is not referenced/accessed by anything else. My goal is to end up with just Table1, but having the data from Table2.
(Note that Table2 and Table1 do not have the same set of id-PRIMARY KEY.)
What is the best way to do this without risking Table1 not existing momentarily for other sessions?
I know I can do something like:
DROP TABLE Table1; ALTER TABLE Table2 RENAME Table1;
But am not sure how to ensure nothing tries to query Table1 while it doesn't exist or is empty. Do I use transactions or lock the tables or something else? (Ideally, I'd want any other sessions' queries to return either old Table1 or new Table1 data rather than failing.)
I am using CodeIgniter in PHP to make the calls.
RENAME TABLE Table1 TO Table1_old, Table2 To Table1;
And then I can DROP Table1_old at my leisure