CODEWITHSUNDEEP
mysqlsqldatabase
Jariel
Jariel

Reputation: 79

MySQL - Update X where Y is lowest

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?

Upvotes: 1

Views: 298

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

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;

Upvotes: 1

Gravy
Gravy

Reputation: 12465

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;

Upvotes: 0

Mahmoud
Mahmoud

Reputation: 883

you can write your query as follow:

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

Upvotes: 0

Related Questions