Re: MySql bug?

Hi,

> UPDATE Prog SET Prog=Prog+1 WHERE Gruppo=16 AND Prog>=1 ORDER BY Prog DESC;
> I know ORDER BY usage in UPDATE but it doesn't work!

What version of MySQL are you using? ORDER BY in UPDATE/DELETE was
introduced in 4.0.

I just tried it, and it works fine:

mysql> CREATE TABLE `prova` (
    ->   `Gruppo` int(11) NOT NULL default '0',
    ->   `Prog` int(11) NOT NULL default '0',
    ->   `Descrizione` varchar(100) default '',
    ->   PRIMARY KEY  (`Gruppo`, `Prog`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO prova VALUES
(16,1,'asd'),(16,2,'asd'),(16,3,'asd'),(16,4,'asd'),(16,5,'asd'),(16,6,'asd'
),(16,7,'asd'),(16,8,'asd'),(16,9,'asd'),(16,10,'asd');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM prova;
+--------+------+-------------+
| Gruppo | Prog | Descrizione |
+--------+------+-------------+
|     16 |    1 | asd         |
|     16 |    2 | asd         |
|     16 |    3 | asd         |
|     16 |    4 | asd         |
|     16 |    5 | asd         |
|     16 |    6 | asd         |
|     16 |    7 | asd         |
|     16 |    8 | asd         |
|     16 |    9 | asd         |
|     16 |   10 | asd         |
+--------+------+-------------+
10 rows in set (0.00 sec)

mysql> UPDATE prova SET Prog=Prog+1 WHERE Gruppo=16 and Prog>=1;
ERROR 1062: Duplicate entry '16-2' for key 1

mysql> UPDATE prova SET Prog=Prog+1 WHERE Gruppo=16 and Prog>=1 ORDER BY
Prog desc;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> SELECT * FROM prova;
+--------+------+-------------+
| Gruppo | Prog | Descrizione |
+--------+------+-------------+
|     16 |    2 | asd         |
|     16 |    3 | asd         |
|     16 |    4 | asd         |
|     16 |    5 | asd         |
|     16 |    6 | asd         |
|     16 |    7 | asd         |
|     16 |    8 | asd         |
|     16 |    9 | asd         |
|     16 |   10 | asd         |
|     16 |   11 | asd         |
+--------+------+-------------+
10 rows in set (0.00 sec)

mysql> SELECT version();
+------------+
| version()  |
+------------+
| 4.0.14-log |
+------------+
1 row in set (0.00 sec)


Rgds,
Indrek

-- 
|  Indrek Siitan, MySQL AB, Support Engineer & Bugmaster
|  Uuemõisa, Haapsalu, Estonia
+- 
|  Are you MySQL Certified? http://www.mysql.com/certification/
--


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]