CODEWITHSUNDEEP
mysql
Frostwolf
Frostwolf

Reputation: 63

MySQL - compare two tables and generate a diff SQL file

In my database, I have a default table (named "mytable") that I download from the internet. The table has say 100 rows and 10 columns (fields).

I am changing some values in the table but I'm not deleting nor inserting any rows or columns.

Say, in the fifth row of the table, I change the value of field "Name" from "Fox" to "Bear".

Then I download the table again from the internet, and I add it to the database with a different name.

So now I have the tables "oldtable" (containing the default values) and "mytable", where only a field in one row is changed.

Now, I want to show to others what I changed in the database and to give them the SQL script that they can run so they apply the same changes. I can't give them my own "mytable" because they can't use it. They also have it and they changed some values in it, as they see fit. They don't want my table, they just want to apply the changes that I made, on top of the changes that they already made to the table.

So I can give them this file named "patch.sql":

connect myDatabase;
update mytable set name="Bear" where name like "Fox";

However, I would like to create such a "patch.sql" file automatically, so I don't have to remember what I have changed and write the script manually. A program can check the differences between the two tables and generate that SQL file automatically.

Is it possible to do that in MySQL console or with any other existing tool?

Update: the table has a primary key.

Upvotes: 4

Views: 1256

Answers (1)

a_vlad
a_vlad

Reputation: 261

if You edit with colleagues different rows, script could be easy.

But if You think You can edit same rows, but different columns, it look like next:

You have 2 tables

table_1:

id, col1, col2, col3
1   10  50  1
2   10  60  9
3   12  50  3
4   12  60  4
5   11  70  5

table_2:

id, col1, col2, col3
1   20  50  1
2   30  60  2
3   12  60  3
4   12  60  5
5   15  77  22

and run this script:

SELECT CONCAT('UPDATE table_1 SET '
                , CASE WHEN t1.col1 != t2.col1 THEN CONCAT(' col1 = ', t2.col1) ELSE '' END
                , CASE WHEN t1.col1 != t2.col1 AND t1.col2 != t2.col2 THEN ', ' ELSE ''END
                , CASE WHEN t1.col2 != t2.col2 THEN CONCAT(' col2 = ', t2.col2) ELSE '' END
                , CASE WHEN t1.col3 != t2.col3 AND (t1.col2 != t2.col2 OR t1.col1 != t2.col1) THEN ', ' ELSE ''END
                , CASE WHEN t1.col3 != t2.col3 THEN CONCAT(' col3 = ', t2.col3) ELSE '' END 
                , CONCAT(' WHERE id = ', t1.id)) as update_txt
FROM table_1 t1 JOIN table_2 t2 ON t1.id = t2.id WHERE t1.col1 != t2.col1 OR t1.col2 != t2.col2 OR t1.col3 != t2.col3

Your result will be:

UPDATE table_1 SET  col1 = 20 WHERE id = 1
UPDATE table_1 SET  col1 = 30,  col3 = 2 WHERE id = 2
UPDATE table_1 SET  col2 = 60 WHERE id = 3
UPDATE table_1 SET  col3 = 5 WHERE id = 4
UPDATE table_1 SET  col1 = 15,  col2 = 77,  col3 = 22 WHERE id = 5

Then You need just copy result column to text file and send to other parts.

This is work for UPDATE, if You plan INSERT / DELETE rows, script will be more complicated, but use the same logic

Upvotes: 1

Related Questions