MySQL compare two tables and return rows that have the same primary key but different data in other fields

Question

I have two structurally identical tables, table2 is a staging ground for new data that will be used in bulk updating table1.

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

So far the closest I have come is 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 query actually works. There was a problem with the data itself. I'm going to go facepalm for a while.

Thank you everyone for your input.

Answer 1

One thing that your not accounting for is nulls. 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)