MySQL - compare two tables and generate diff SQL file

Question

In my database, I have a default table (called "mytable") that I download from the Internet. The table shows 100 rows and 10 columns (fields).

I am changing some values ​​in the table, but I am not deleting or inserting either rows or columns.

Let's say in the fifth row of the table I change the value of the "Name" field 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 default values) and "mytable", where only the field in one row is changed.

Now I want to show others what I changed in the database and provide them with a SQL script that they can run so that they apply the same changes. I cannot give them my “my table” because they cannot use it. They also have it, and they have changed some of the values ​​in it as they see fit. They do not want my table, they just want to apply the changes that I made, in addition to the changes that they have already made in the table.

Therefore, 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 file “patch.sql” automatically, so I don’t need to remember what I changed and write a script manually. The program can check the differences between the two tables and automatically generate this SQL file.

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

: .

Answer 1

, script .

, , , :

2

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

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

:

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

.

UPDATE, INSERT/DELETE, script ,