Looks correct to me.
What do you think is the problem?
2 rows were affected by the replace.
One row was deleted, and one was inserted,
Both rows had a value of 'test' for column 'Tab'.
Martin MOKREJŠ wrote:
> 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>
>
---------------------------------------------------------------------
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
