rbennett485 rbennett485 - 23 days ago 6
MySQL Question

Update a table inner joined with itself in MySQL

I am trying to update a table in a fairly complex way, and decided it would be useful to inner join the table with itself.

(To give a bit of background, the table stores short chains of records linked through a chain_id field, and ordered within the chain by a date field. The update needs to handle the symbol with the most recent date differently to the others in its chain, and may also need to split some chains into multiple separate chains).

This is a simplified version of what I saw:

CREATE TABLE t(
`a` INT(3) NOT NULL,
`b` INT(3)
);

INSERT INTO t VALUES (3,4),(3,6);

SELECT * FROM t;

------------
| a | b |
-------------
| 3 | 4 |
| 3 | 6 | as expected


UPDATE t t1
INNER JOIN t t2
ON (t1.a = t2.a
AND t1.b > t2.b)
SET t1.b = t1.a
AND t2.b = t1.a;

SELECT * FROM t;

------------
| a | b |
-------------
| 3 | 4 |
| 3 | 0 | wat


What I would have hoped to get was:

------------
| a | b |
-------------
| 3 | 3 |
| 3 | 3 |


So clearly this was a bad plan, but leaves me with three questions:

1.) What is going on here?

2.) Since whatever is going on is unlikely to be what was intended, why does MySQL allow it at all?

3.) Is there another way to achieve something like I intended (i.e. actually update a table inner joined with itself, not just get everything set to 3...)

Answer

@CGritton explained why only 1 record was updated, but did not provide an exact information as to why b field was set to 0 in case of the 2nd record.

The 1st clue to the solution is that you got the set clause wrong. If you want to update 2 fields, then you need to separate the two assignments with comma, not with an and operator. The correct statement would be:

UPDATE t t1
INNER JOIN t t2
ON (t1.a = t2.a
AND t1.b > t2.b)
SET t1.b = t1.a, t2.b = t1.a;

If we analyse the original set clause:

SET t1.b = t1.a AND t2.b = t1.a

Note, that the assignment operator has lower precedence, then the and operator, so the above expression will be executed as:

SET t1.b = ((t1.a AND t2.b) = t1.a)

If I substitute the numbers in place of the field names:

SET t1.b = ((3 AND 4) = 3)

(3 AND 4) is 0, 0 = 3 is 0, therefore t1.b will be set to 0

I do not fully understand what you would like to achieve with your query, so I cannot suggest a final version for you. But the first corrected version should be a good starting point for you. Just make sure that you understand your join criteria.