MySQL compares two tables and returns rows that have the same primary key, but different data in different fields

Question

I have two structurally identical tables, table2 is an intermediate platform for new data that will be used in the update mass table.

I need to find out which rows will be updated in table1. I want to ignore the rows that will be inserted, and those that will be deleted. I'm just interested in updated rows where the primary key remains the same, but one or more other fields in the row contain different data.

So far, the closest I have come to the following statement.

SELECT table2.* FROM table2 INNER JOIN table1 ON table1.primarykey = table2.primarykey WHERE table1.field1 != table2.field1 OR table1.field2 != table2.field2 OR table1.field3 != table2.field3 

This returns 0 rows.

EDIT: The request really works. There was a problem with the data itself. I am going to go facepalm for a while.

Thank you all for your input.

Answer 1

One thing you don't consider is null. This may or may not be your problem, as it depends on the data.

 SELECT table2.* FROM table2 INNER JOIN table1 ON table1.primarykey = table2.primarykey WHERE table1.field1 != table2.field1 OR table1.field2 != table2.field2 OR table1.field3 != table2.field3 OR (table1.field1 is null and table2.field1 is not null) OR (table2.field1 is null and table1.field1 is not null) OR (table1.field2 is null and table2.field2 is not null) OR (table2.field2 is null and table1.field2 is not null) OR (table1.field3 is null and table2.field3 is not null) OR (table2.field3 is null and table1.field3 is not null) 

Answer 2

In other words, you want to count in table2 how many rows the primary key has in the same way as in table1, and at least one of the fields is different, right? But how important is it if the data is different or not? If the data matches, UPDATE will have no effect.

 SELECT COUNT(T2.*) FROM table2 AS T2 JOIN table1 AS T1 ON (T1.primarykey = T2.primarykey);