CODEWITHSUNDEEP
mysqldatabase
gelviis
gelviis

Reputation: 458

Updated with join in MySQL query not working

I've tried lots of versions of this update query using answers from previous questions but can't seem to get it to work. The latest one I have returns 0 rows affected.

I get 2017 results when I run this query (which is what I want)

SELECT *
FROM  table_1 t1
INNER JOIN table_2 t2 ON t1.company = t2.company
WHERE t1.user = 123 AND t2.group_id = 3

But I want to run an update query like this

UPDATE table_1 AS t1
INNER JOIN table_2 as t2
ON t1.company = t2.company
SET t1.user = t2.user
WHERE t1.user = 123 AND t2.group_id = 3

But I get 0 rows affected

Why does this update query not update the 2017 records that are returned in query 1?

Upvotes: 0

Views: 850

Answers (2)

Ian Clelland
Ian Clelland

Reputation: 44172

Are you sure that the table_1.user and table_2.user values are actually different before you run the update query? MySQL will report the number of rows that were actually changed, not the number of rows that were selected and examined.

Try running this query first, to see how many of your 2017 rows actually need updating:

SELECT *
FROM  table_1 t1
INNER JOIN table_2 t2 ON t1.company = t2.company
WHERE t1.user = 123 AND t2.group_id = 3
AND t1.user <> t2.user

Update

It turns out that this can happen if the data types are similar enough to be compared, but not enough that the values in table_2.user can be assigned to table_1.user. In that case, MySQL will issue a warning ("Out of range value adjusted for column 'user' at row xxx"), but will otherwise allow the query to succeed. Running the query again will show that the values are still different, but won't change what is actually stored in t1.user.

If you modify the update query to only match the rows that need to be changed, and run this from the command line, then MySQL will tell you exactly what's going on:

mysql> UPDATE table_1 AS t1
    -> INNER JOIN table_2 as t2
    -> ON t1.company = t2.company
    -> SET t1.user = t2.user
    -> WHERE t1.user = 123 AND t2.group_id = 3
    -> AND t1.user <> t2.user
Query OK, 0 rows affected, 2017 warnings (0.01 sec)
Rows matched: 2017  Changed: 0  Warnings: 2017

Rows matched: 2017 indicates that there were 2017 rows that need to be updated (if the values were already correct, this would be 0)

Changed: 0 means that none of them actually ended up with different values, despite the SET t1.user = t2.user command.

Warnings: 2017 says that something went wrong, and you should issue a SHOW WARNINGS query to find out what it was. In this case, the disparity between what can be stored in a smallint vs an integer meant that the data was being truncated in table_1.

Upvotes: 0

James Black
James Black

Reputation: 41858

I expect your select query is returning too many rows, as you have an error:

INNER JOIN table_2 t2 ON t2.company = t2.company

I think you want:

INNER JOIN table_2 t2 ON t1.company = t2.company

Upvotes: 1

Related Questions