compare 2 mysql tables are identical and if not make them
Posted: Wed Feb 03, 2010 8:05 pm
Hi I have a bit of an unusual request, I am developing an accounts program which we use in our business while under development (unfortunatly).
I have a table in mysql that stores our customer details called "users". When a customer cancels there account we copy the data from "users" to "deleted_users" using a simple "select * from users insert into deleted_users" mysql statement and this works great AS LONG AS the columns in each table are identical.
The problem is because the program is still in development I may occasionally add a column in "users" and forget to add it in "deleted_users" then the customers details are lost.
So what I want is a code snippet (if anybody has one) that checks my "users" table and my "deleted_users" table and makes sure they are identical (column wise) if not it makes "deleted_users" identical to "users".
I have hunted around for a while and I have come up with a solution that I could pursue by getting each column from the "users" table in a while statement then see if that column is in "deleted_users" and if not add it however that method seems excessively long winded and I was hoping there may be an easier way before I spend a lot of time on this problem.
I have a table in mysql that stores our customer details called "users". When a customer cancels there account we copy the data from "users" to "deleted_users" using a simple "select * from users insert into deleted_users" mysql statement and this works great AS LONG AS the columns in each table are identical.
The problem is because the program is still in development I may occasionally add a column in "users" and forget to add it in "deleted_users" then the customers details are lost.
So what I want is a code snippet (if anybody has one) that checks my "users" table and my "deleted_users" table and makes sure they are identical (column wise) if not it makes "deleted_users" identical to "users".
I have hunted around for a while and I have come up with a solution that I could pursue by getting each column from the "users" table in a while statement then see if that column is in "deleted_users" and if not add it however that method seems excessively long winded and I was hoping there may be an easier way before I spend a lot of time on this problem.