Page 1 of 1

compare 2 mysql tables are identical and if not make them

Posted: Wed Feb 03, 2010 8:05 pm
by terry1738
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.

Re: compare 2 mysql tables are identical and if not make them

Posted: Wed Feb 03, 2010 8:11 pm
by Eran
Why not having a 'status' column in the users table that would indicate if users are deleted or not?

Re: compare 2 mysql tables are identical and if not make them

Posted: Thu Feb 04, 2010 12:46 am
by terry1738
Thanks yes I agree, That would have been an option 6 years ago but the previous programmer decided to do it like this and I dont know without even more work what depends on this feature such as reestablishing the client when they return and how it fits into the billing if any monies are owing when the client cancels.
Just thought I might be able to save myself some work if someone else had run up against a similar issue.
Currently there are in excess of 11000 clients in the deleted_users table. So its a matter of take the easiest path
edit: Strangly the table does have a status column which was used for deleted and is now used for disabled

Re: compare 2 mysql tables are identical and if not make them

Posted: Thu Feb 04, 2010 2:37 am
by Eran
Personally I would move the deleted_users rows back to users with a different status value than disabled. That would be the normalized approach to storing such data, especially if deleted users can return to active status.
If that's not an option, the ezComponents library has a component for comparing database schemas and applying the results.
http://www.ezcomponents.org/docs/tutori ... se-schemas

Re: compare 2 mysql tables are identical and if not make them

Posted: Thu Feb 04, 2010 5:46 pm
by terry1738
Thanks so much for directing me Thats a great resource I knew nothing about, I can see already how I can implement what I want.
Also I take your point about the status column, it will take quiet a deal of work to implement but I see it as an important part of any rewrite.
Thanks again for taking the time. Much appreciated

Re: compare 2 mysql tables are identical and if not make them

Posted: Thu Feb 04, 2010 11:38 pm
by Benjamin
INSERT INTO [table] [fields] SELECT [fields] FROM [table2] WHERE NOT EXISTS (SELECT [field] FROM [table2] WHERE [field] = [field])

I'm sure you could work a little magic with MySQL.