When I do an update with inner joins, changing the clause ON columns affects the update?

Question

For example if you have this query:

UPDATE table1 
INNER JOIN table2 ON table1.id = table2.id 
SET table2.id = table1.idSetting 
WHERE table2.property IS NULL

You can note that the table2 id's changes when the table2.property is null, my question is: if this will affect the "affected rows" when the query is running or not.

I do this question because I'm trying to do an update similar to this that needs to update some columns that are in the inner join clauses, but is suposed that will affect 36000 rows but finally only affects 4500 rows.

Thanks.

Answer 1

Affected rows are counted as those rows that really get changed to a different value as a result of the update. If they already had the value you're setting them to, they don't count as an "affected" row.

You can test:

SELECT COUNT(*) FROM table1 
INNER JOIN table2 ON table1.id = table2.id 
WHERE table2.property IS NULL

Does the above return 36000 or 4500? It should return 36000 if that's how many rows match the conditions.

SELECT COUNT(*) FROM table1 
INNER JOIN table2 ON table1.id = table2.id 
WHERE table2.property IS NULL
AND table2.id <> table1.idSetting

Whereas that may return 4500.