MySql bug?
am 17.09.2003 08:29:25 von Andrea
Hi.
In MySql I create this table:
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;
Running this:
UPDATE Prog SET Prog=Prog+1 WHERE Gruppo=16 AND Prog>=1
I get "Duplicate entry".
Is this a MySql bug? How can I do this?
Tnx, bye.
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org
Re: MySql bug?
am 17.09.2003 10:22:40 von indrek siitan
Hi,
> In MySql I create this table:
>=20
> 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=3DMyISAM;
>=20
> Running this:
> UPDATE Prog SET Prog=3DProg+1 WHERE Gruppo=3D16 AND Prog>=3D1
>=20
> I get "Duplicate entry".
>=20
> Is this a MySql bug? How can I do this?
This is not a bug, but a shortcoming of MyISAM tables not being
transactional. But you can overcome this by using the following query:
UPDATE Prog SET Prog=3DProg+1 WHERE Gruppo=3D16 AND Prog>=3D1
ORDER BY Prog DESC;
Rgds,
Indrek
--=20
| Indrek Siitan, MySQL AB, Support Engineer & Bugmaster
| Uuem=F5isa, Haapsalu, Estonia
+-=20
| 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/bugs?unsub=3Dgcdmb-bugs@m.gmane.org
Re: MySql bug?
am 17.09.2003 12:12:04 von indrek siitan
Hi,
> UPDATE Prog SET Prog=3DProg+1 WHERE Gruppo=3D16 AND Prog>=3D1 ORDER BY Prog DES=
C;
> 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=3DMyISAM;
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,'a sd'),(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=3DProg+1 WHERE Gruppo=3D16 and Prog>=3D1;
ERROR 1062: Duplicate entry '16-2' for key 1
mysql> UPDATE prova SET Prog=3DProg+1 WHERE Gruppo=3D16 and Prog>=3D1 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
--=20
| Indrek Siitan, MySQL AB, Support Engineer & Bugmaster
| Uuem=F5isa, Haapsalu, Estonia
+-=20
| 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/bugs?unsub=3Dgcdmb-bugs@m.gmane.org
Re: MySql bug?
am 17.09.2003 15:14:25 von Alexander Keremidarski
Andrea,
Andrea wrote:
> ERRATA CORRIGE:
>
> I'm using MySql v.4.0.12-nt but this sql code continue giving me the same
> error! (Duplicate entry)
>
> UPDATE Prova SET Prog=Prog+1 WHERE Gruppo=16 AND Prog>=1 ORDER BY Prog DESC;
It works as Indrek described in 4.0.15. There are much more reasons to upgrade to
it so please do it.
Check manual for changes. There are plenty of them in last 6 months between 4.0.12
and 4.0.15
http://www.mysql.com/doc/en/News-4.0.x.html
http://www.mysql.com/doc/en/News-4.0.13.html
http://www.mysql.com/doc/en/News-4.0.14.html
http://www.mysql.com/doc/en/News-4.0.15.html
--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org