Hello,
I think I've found a bug. Just by playing with REPLACE and INSERT, the
following happened. I'm including full log, demonstrating that I don't
understand sql at all. ;-) Can someone explain me what "2 rows affected"
means? How can I select the second row from the table? How can I delete it?
Please CC me in reply. Thanks.
mysql> select * from Upd;
+------------------------+---------------------+
| Tab | Time |
+------------------------+---------------------+
| prot_data | 2001-07-20 00:45:44 |
| contig_data | 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data | 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
| blimps_data | 2001-11-12 15:37:55 |
| pros_data | 2001-11-12 15:38:08 |
| prd_data | 2001-11-12 15:39:16 |
| scop1_data | 2001-09-02 16:34:01 |
| scop2_data | 2001-09-02 17:05:52 |
| blast_crossupdate_data | 2001-08-23 18:05:12 |
| blast_self_data | 2001-10-03 05:15:48 |
| intergenome_data | 2001-10-23 20:56:48 |
+------------------------+---------------------+
21 rows in set (0.02 sec)
mysql> replace into Upd Tab="test" Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Tab="test" Time=NOW()' at line 1
mysql> replace into Upd Tab="test" Time="2";
ERROR 1064: You have an error in your SQL syntax near 'Tab="test" Time="2"' at line 1
mysql> replace into Upd Tab="test" Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Tab="test" Time=NOW()' at line 1
mysql> replace into Upd set Tab="test" Time=NOW();
ERROR 1064: You have an error in your SQL syntax near 'Time=NOW()' at line 1
mysql> replace into Upd (Tab,Time) values ('test', NOW());
Query OK, 1 row affected (0.02 sec)
mysql> update into Upd (Tab,Time) values ('test', NOW());
ERROR 1064: You have an error in your SQL syntax near 'into Upd (Tab,Time) values
('test', NOW())' at line 1
mysql> update Upd (Tab,Time) values ('test', NOW());
ERROR 1064: You have an error in your SQL syntax near '(Tab,Time) values ('test',
NOW())' at line 1
mysql> update Upd set Time=NOW() where Tab="test";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> replace into Upd (Tab,Time) values ('test', NOW());
Query OK, 2 rows affected (0.02 sec)
mysql> select * from Upd;
+------------------------+---------------------+
| Tab | Time |
+------------------------+---------------------+
| prot_data | 2001-07-20 00:45:44 |
| contig_data | 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data | 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
| blimps_data | 2001-11-12 15:37:55 |
| pros_data | 2001-11-12 15:38:08 |
| prd_data | 2001-11-12 15:39:16 |
| scop1_data | 2001-09-02 16:34:01 |
| scop2_data | 2001-09-02 17:05:52 |
| blast_crossupdate_data | 2001-08-23 18:05:12 |
| blast_self_data | 2001-10-03 05:15:48 |
| intergenome_data | 2001-10-23 20:56:48 |
| test | 2001-11-13 22:39:40 |
+------------------------+---------------------+
22 rows in set (0.02 sec)
mysql> insert into Upd (Tab,Time) values ('test', NOW());
ERROR 1062: Duplicate entry 'test' for key 1
mysql> select * from Upd;
+------------------------+---------------------+
| Tab | Time |
+------------------------+---------------------+
| prot_data | 2001-07-20 00:45:44 |
| contig_data | 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data | 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
| blimps_data | 2001-11-12 15:37:55 |
| pros_data | 2001-11-12 15:38:08 |
| prd_data | 2001-11-12 15:39:16 |
| scop1_data | 2001-09-02 16:34:01 |
| scop2_data | 2001-09-02 17:05:52 |
| blast_crossupdate_data | 2001-08-23 18:05:12 |
| blast_self_data | 2001-10-03 05:15:48 |
| intergenome_data | 2001-10-23 20:56:48 |
| test | 2001-11-13 22:39:40 |
+------------------------+---------------------+
22 rows in set (0.02 sec)
mysql> replace into Upd (Tab,Time) values ('test', NOW());
Query OK, 2 rows affected (0.00 sec)
mysql> insert into Upd (Tab,Time) values ('test', NOW());
ERROR 1062: Duplicate entry 'test' for key 1
mysql> replace into Upd (Tab,Time) values ('test', NOW());
Query OK, 2 rows affected (0.00 sec)
mysql> select * from Upd;
+------------------------+---------------------+
| Tab | Time |
+------------------------+---------------------+
| prot_data | 2001-07-20 00:45:44 |
| contig_data | 2001-07-20 00:45:47 |
| orf_data | 2001-07-20 00:45:48 |
| blast_data | 2001-11-12 15:19:13 |
| seg_data | 2001-11-12 15:19:29 |
| nonglob_data | 2001-11-12 15:19:35 |
| pfam_data | 2001-11-12 15:33:25 |
| scop_data | 2001-11-12 15:33:41 |
| mem_data | 2001-11-12 15:33:47 |
| coils_data | 2001-11-12 15:33:54 |
| funcat_data | 2001-11-12 15:34:15 |
| cogs_data | 2001-11-12 15:34:59 |
| known3d_data | 2001-11-12 15:35:11 |
| blimps_data | 2001-11-12 15:37:55 |
| pros_data | 2001-11-12 15:38:08 |
| prd_data | 2001-11-12 15:39:16 |
| scop1_data | 2001-09-02 16:34:01 |
| scop2_data | 2001-09-02 17:05:52 |
| blast_crossupdate_data | 2001-08-23 18:05:12 |
| blast_self_data | 2001-10-03 05:15:48 |
| intergenome_data | 2001-10-23 20:56:48 |
| test | 2001-11-13 22:41:21 |
+------------------------+---------------------+
22 rows in set (0.02 sec)
mysql> select * from Upd where Tab="test";
+------+---------------------+
| Tab | Time |
+------+---------------------+
| test | 2001-11-13 22:41:21 |
+------+---------------------+
1 row in set (0.02 sec)
mysql>
mysql> show columns from Upd;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Tab | varchar(100) | | PRI | | |
| Time | varchar(100) | | | | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql>
mysql> explain select * from Upd where Tab="test";
+-------+-------+---------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+---------+---------+-------+------+-------+
| Upd | const | PRIMARY | PRIMARY | 100 | const | 1 | |
+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.02 sec)
mysql>
--
Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs
MIPS / Institute for Bioinformatics <http://mips.gsf.de>
GSF - National Research Center for Environment and Health
Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany
tel.: +49-89-3187 3616 , fax: +49-89-3187 3585
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
