Erlaunis Erlaunis - 2 months ago 10
MySQL Question

update executed on every row of the table

I have the following SQL :

DROP TABLE IF EXISTS scores;

CREATE TABLE scores
(
id INTEGER PRIMARY KEY,
nom VARCHAR(10),
score INTEGER,
rang INTEGER
);

INSERT INTO scores
VALUES (1,'a',91,11),
(2,'b',92,12),
(3,'c',93,13),
(4,'d',94,14);

UPDATE scores
SET nom = 'foo',
score = 1,
rang = 0
WHERE id = (SELECT id
ORDER BY score DESC
LIMIT 1);


I want to update only the row with the highest score (i.e. 94) but when I execute the request, every line of the table are taking these values (demo).

+----+-----+-------+------+
| id | nom | score | rang |
+----+-----+-------+------+
| 1 | foo | 1 | 0 |
| 2 | foo | 1 | 0 |
| 3 | foo | 1 | 0 |
| 4 | foo | 1 | 0 |
+----+-----+-------+------+


I don't understand why ? Can someone help me please ?

Answer

You can use order by and limit in an update in MySQL. So, I think you mean:

UPDATE scores
    SET nom = 'foo', score = 1, rang = 0
    ORDER BY score DESC
    LIMIT 1;