Bug #65766 insert/update
Submitted: 29 Jun 2012 7:08 Modified: 29 Jul 2012 10:08
Reporter: chandra kishore Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S5 (Performance)
Version: OS:Windows
Assigned to: CPU Architecture:Any

[29 Jun 2012 7:08] chandra kishore 
Description:
Hi 
I am using MySql WorkBench

I have created a table with columns name,date and time 
and added rows
the problem i am facing is
i want to insert a column if not exists in table
update if exists 
i used query 

insert into start(name,date,time) values('chandra',CURDATE(),CURTIME())
ON DUPLICATE KEY
UPDATE time = CURTIME()

but it is creating a new row instead of updating ..

the same above code is working for strings

and indexes are

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment

Please Help Me

How to repeat:
I need To update if already exists or insert  if not  on table
[29 Jun 2012 7:15] Valeriy Kravchuk 
What exact server version, 5.x.y, are you working with?

Please, send exact CREATE TABLE for that start table and data (INSERTs) to demonstrate the problem.
[29 Jun 2012 9:36] chandra kishore 
hi
I am using MYSQL5.5.24.0 
i created table 
create table start(name VARCHAR(20),date DATE,time TIME);
[29 Jun 2012 10:08] Valeriy Kravchuk 
You have to define some UNIQUE (or PRIMARY) key on table for this to work. Like this:

mysql> create table start(name VARCHAR(20),date DATE,time TIME);
Query OK, 0 rows affected (0.27 sec)

mysql> alter table start add unique key (name);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into start(name,date,time) values('chandra',CURDATE(),CURTIME())
    -> ON DUPLICATE KEY
    -> UPDATE time = CURTIME();
Query OK, 1 row affected (0.06 sec)

mysql> select * from start;
+---------+------------+----------+
| name    | date       | time     |
+---------+------------+----------+
| chandra | 2012-06-29 | 13:07:07 |
+---------+------------+----------+
1 row in set (0.00 sec)

mysql> insert into start(name,date,time) values('chandra',CURDATE(),CURTIME())
    -> ON DUPLICATE KEY
    -> UPDATE time = CURTIME();
Query OK, 2 rows affected (0.05 sec)

mysql> select * from start;
+---------+------------+----------+
| name    | date       | time     |
+---------+------------+----------+
| chandra | 2012-06-29 | 13:07:21 |
+---------+------------+----------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.25    |
+-----------+
1 row in set (0.00 sec)
[30 Jul 2012 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".