Reputation: 153
I have problem while dealing with copy of database table entry from one file to other .
suppose i have two tables called one.sql , two.sql , now i wana transfer some record from one.sql to the table two.sql, then delete that entry from one.sql ater copying success.
problem : suppose power was gone after i make copy from one to two, the delete record from one wasnt done , here in that case same record will be in both tables. that i dont want. so in this situation how to handle these types of inconsistent on fly.
Upvotes: 0
Views: 366
Reputation: 4101
You can use transaction blocks to descrease unexpected results at some degree. But solving a power problem is another thing.
You can however use a batch and check if two tables don't contain same records at some interval, if you are worried about a power problem.
Upvotes: 1
Reputation: 52030
Your RDBMS is not a simple datastore! It supports journaling, transaction isolation and atomic updates. So...
... with transactional tables (InnoDB) and with decent isolation level simply do:
START TRANSACTION -- Or SET autocommit = 0
INSERT INTO two SELECT * FROM one WHERE ...;
DELETE FROM one WHERE ...;
COMMIT
COMMIT
will atomicity apply the changes to the DB. That is, from the other transactions point of view, the move is either done or not started. No one can see it half done. Even in case of catastrophic failure (power outage).
Of course, if you move all your records, you could also rely on RENAME TABLE
...
Upvotes: 3