CODEWITHSUNDEEP
mysqljoinsql-update
bfavaretto
bfavaretto

Reputation: 71939

UPDATE affecting less rows than equivalent SELECT

The following query returns 2303 rows:

SELECT a.* 
FROM cur_analises a
    INNER JOIN cur_materias_subsidiarias ms
    ON ms.materia_id = a.materia_id
    AND ms.subsidiaria_id IN(SELECT id FROM cur_subsidiarias WHERE cliente_id = 134)
    INNER JOIN cur_materias m
    ON m.id = a.materia_id
    INNER JOIN cur_clientes c
    ON c.carga_id = ms.subsidiaria_id
WHERE a.cliente_id = 134;

I need to update the cliente_id field of all those 2303 rows from table cur_analises with the value from cur_clientes.id. However, when I try to turn that SELECT query into the following UPDATE, it only affects 2297 rows, according to MySQL Workbench:

UPDATE cur_analises a
    INNER JOIN cur_materias_subsidiarias ms
    ON ms.materia_id = a.materia_id
    AND ms.subsidiaria_id IN(SELECT id FROM cur_subsidiarias WHERE cliente_id = 134)
    INNER JOIN cur_materias m
    ON m.id = a.materia_id
    INNER JOIN cur_clientes c
    ON c.carga_id = ms.subsidiaria_id
SET a.cliente_id = c.id
WHERE a.cliente_id = 134;

I have no idea why it's missing 6 rows. What am I doing wrong?

Upvotes: 1

Views: 830

Answers (2)

Yamen Ashraf
Yamen Ashraf

Reputation: 2960

We had a similar case, the issue was with mysql partitioning. Once we revert partitioning everything is ok again.

Upvotes: 0

Reimius
Reimius

Reputation: 5712

You are probably not joining on a unique value or set of values at some point in the query causing denormalization of your result set. Then when you do the update it only updates the rows that actually meet the criteria of the joins for the table aliased as a. Only you can know what are truly non denormalizing joins in your query and fix them.

Upvotes: 4

Related Questions