MySQL - Update X where Y is lowest

Question

I have following table:

       TableA
  x               y
----------------------
 3                1
 2                5
 5                2

Now i want to update the value in column x, where the value in column y is lowest, something like that:

UPDATE TableA SET x = 1 WHERE y = min(y);

Then the first row should be x = 1 and y = 1.

But i get following error code:

Error Code: 1111. Invalid use of group function

How can i fix that?

You are getting that error because `min` is an aggregate function and you need a `group by` clause to use aggregate functions. — Jeff Burgin, Oct 07 '15 at 19:11
Here is an answer that may be helpful http://stackoverflow.com/a/5272523 — Jeff Burgin, Oct 07 '15 at 19:13
@Jeff Burgin Yes, but update doesn't support group by edit: ok i'll check out the link — Jariel, Oct 07 '15 at 19:14

Gordon Linoff · Accepted Answer · 2015-10-07T19:20:45.353

1

If y is unique, then you can use order by and limit:

UPDATE TableA
    SET x = 1 
    ORDER BY Y ASC
    LIMIT 1;

If you have multiple rows with the minimum value, you can use join:

UPDATE TableA a JOIN
       (SELECT MIN(Y) as miny FROM TableA) m
       ON a.y = m.miny
    SET x = 1;

edited Oct 07 '15 at 19:20

answered Oct 07 '15 at 19:13

Gordon Linoff

score 0 · Answer 2 · answered Oct 07 '15 at 19:13

0

you can write your query as follow:

UPDATE TableA SET x = 1 WHERE y IN (select min(y) from TableA T2);

answered Oct 07 '15 at 19:13

Mahmoud

In MySQL you can't reference the table being updated. – Gordon Linoff Oct 07 '15 at 19:14

score 0 · Answer 3 · answered Oct 07 '15 at 19:14

0

UPDATE TableA
    JOIN (SELECT min(y) as min_y, FROM TableA GROUP BY y) 
      as temp ON temp.min_y = TableA.y
   SET x = 1;

answered Oct 07 '15 at 19:14

Gravy

3 Answers3