INSERT... ON DUPLICATE KEY UPDATE issue

[thara]

I am having a mysql table named `company_profile`. It may have only one record. So I tried to insert and update data of the table using `INSERT.... ON DUPLICATE KEY UPDATE` query.

My query is something like this:

    $sql = "INSERT INTO company_profile (
                          company_name
                        , tel
                        , mobile
                        , fax
                        , email
                     ) VALUES (?, ?, ?, ?, ?)
                    ON DUPLICATE KEY UPDATE
                          company_name= VALUES(company_name)
                        , tel           = VALUES(tel)
                        , mobile       = VALUES(mobile)
                        , fax           = VALUES(fax)
                        , email        = VALUES(email)";                
    
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('sssss', $company_name
                             , $telephone
                             , $mobile
                             , $fax
                             , $email
                            );
    $stmt->execute();    

My problem is when I updating the data, it always inserting a new record into my table.

Can anybody tell me what would be the problem of this?

My table structure looks like this:
 

    CREATE TABLE IF NOT EXISTS company_profile (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        company_name VARCHAR(120) NOT NULL,    
        tel VARCHAR(20) NOT NULL,    
        mobile VARCHAR(20) NOT NULL,    
        fax VARCHAR(20) DEFAULT NULL,    
        email VARCHAR(60) NOT NULL,    
        PRIMARY KEY (id),
        UNIQUE KEY (id)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[Barand]

You aren't inserting the id into the new record so you are not attempting to create a duplicate

Edited December 20, 2015 by Barand

[thara]

@Barand,

 

I tried it something like this:

$sql = "INSERT INTO company_profile (
                      id
                    , company_name
                    , tel
                    , mobile
                    , fax
                    , email
                 ) VALUES (1, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                        company_name= VALUES(company_name)
                    , tel                 = VALUES(tel)
                    , mobile             = VALUES(mobile)
                    , fax                 = VALUES(fax)
                    , email              = VALUES(email)"; 

But still inserting new record...

[Barand]

Did you already have a record with an id of "1"?

 

The id would be the id of the record to be updated, not 1 every time.

[thara]

Sir, there should be only one record set in the table. Thats why I set 1 for id.

[Barand]

So what are the ids of the records now in your table?

[Barand]

If there should only ever be a single record in that table

• why have an auto_incrementing id
• why even attempt to insert a new record? Just update the existing one

[thara]

Yes sir, it is not needed auto_increment id, so  I changed my table structure got it to work. At first, when using these data, it should be insert and after inserting it should be use update. Thats why I tried to used INSERT.... ON DUPLICATE KEY UPDATE query.

 

Sir, can we get it to work adding an unique key to "company_name" column, like this

UNIQUE KEY (company_name)

[Barand]

As long as your existing id is "1" then the version you had in #3 should work. (It worked for me)

[thara]

Yes sir, I got it to work. In #8 I asked other method can we use. isn't it?

[Barand]

Seems a pointless question but, yes, so long as you don't want to change the company name.

[Psycho]

thara, you are missing the point. There was nothing, specifically, wrong with the query you were using in post #3. What Barand was asking was if the existing record actually had an id of '1'. Your response was that there should only ever be one record. But, that doesn't mean that record will have an id of 1. Since it is an auto-increment value, the first record would normally have an id of 1 if not specified in the INSERT statement. But, that id could have been specifically set to another value in the INSERT statement, changed later, or if you deleted the first record and created a new one (which could happen while testing/debugging) the id could be anything.

 

So, to answer your question in #10 - which is the same question you asked in #1 and #3, you can use ON DUPLICATE KEY UPDATE using any field as a unique key as long as you include the value of that field in the query and the value is a duplicate of an existing record. Changing from id to company_name makes no difference.