Update max value from a mysql table

Question

I'm trying to update a table's value using max(col) in a subquery, but for some reason, it's updating all values that match the user_id column I'm using.

This is my table structure:

user_id | steps | in_date
--------+-------+-----------
8       |10     | 1522246892
8       |10     | 1522250713
7       |10     | 1522250799

And this is my query:

UPDATE userdata
    SET steps = (steps + 20)
    WHERE user_id = 8
    AND in_date = (SELECT max(in_date) WHERE user_id = 8);

I expected it to update only the second column, but it instead updates both columns with user_id = 8. Why isn't it working as expected? What am I doing wrong?

Edit: Thanks to Manoj's comment, I changed the query to the following, and it works:

UPDATE userdata 
    SET steps = (steps + 20)
    WHERE user_id = 8
    ORDER BY in_date DESC LIMIT 1;

Doing it his way is even better, since I don't have to run two queries, and already gets the highest one by id.

Make sure that your select is from the same table you are showing us. — EzLo, Apr 03 '18 at 09:22
is clear to .me .. check for your actual real data content .. and if you are in console or sql ide .. try disconnect and reconnect — ScaisEdge, Apr 03 '18 at 09:24
@EzequielLópezPetrucci I just checked by your comment. I tried `WHERE userdata.user_id = 8`, but still same result. Didn't think about that possibility! — Newbb, Apr 03 '18 at 09:25

score 1 · Accepted Answer · answered Apr 03 '18 at 09:36

1

You will encounter sql error "You can't specify target table 'userdata' for update in FROM clause" when you use same table in the sub-query to update the same table.

Its strange that you say its running because, - you missed from clause in your sub-query - you can't use same table

Can you please be more specific.

answered Apr 03 '18 at 09:36

Manojkumar B

206
1
7

Exactly. This is why I'm using `userdata.user_id`. What do you mean by being more specific? I'll try to provide all info you need. – Newbb Apr 03 '18 at 09:40
can u try this: ** UPDATE userdata SET steps = (steps + 20) WHERE user_id = 8 order by in_date desc limit 1; ** – Manojkumar B Apr 03 '18 at 09:45
Ohh, it works! That is much better, thank you! I didn't think about that. – Newbb Apr 03 '18 at 09:48
Happy that helped you @Newbb and may others :) – Manojkumar B Apr 03 '18 at 09:53

ScaisEdge · Answer 2 · 2018-04-03T09:36:59.737

0

seems you missed the from clause in subselect and for avoid conflit between select and updated value you could build a temp table using ainner subselect

UPDATE userdata
SET steps = (steps + 20)
WHERE user_id = 8
AND in_date = ( select max_date from (
          SELECT max(in_date) max_date FROM userdata  WHERE  user_id = 8) t);

and in this way the AND clause could be always true

edited Apr 03 '18 at 09:36

answered Apr 03 '18 at 09:31

ScaisEdge

131,976
10
91
107

I'm sorry, but that's not allowed in mysql. I'd either have to use `userdata.column`, or use an inner join. [Here](https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) is another question where they reply that. Thanks for the answer anyways! – Newbb Apr 03 '18 at 09:35

Update max value from a mysql table

2 Answers2