MySQL比较两个表并返回具有相同主键但在其他字段中具有不同数据的行

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

我有两个结构相同的表,table2是将用于批量更新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.

我需要找出table1中要更新的行。我想忽略那些将要插入的行和那些将被删除的行。我只对更新的行感兴趣,其中主键保持不变,但行中的一个或多个其他字段包含不同的数据。

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.

这返回0行。

EDIT: The query actually works. There was a problem with the data itself. I'm going to go facepalm for a while.

编辑:查询实际上有效。数据本身存在问题。我要去facepalm一段时间。

Thank you everyone for your input.

谢谢大家的意见。

2 个解决方案

#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)

#2

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

换句话说,你想在table2中计算有多少行的主键与table1中的相同,并且至少有一个字段不同,对吧?但是,如果数据不同,重要性如何?如果数据相同,UPDATE将无效。

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