Jump to content

INSERT... ON DUPLICATE KEY UPDATE issue


thara

Recommended Posts

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;
Link to comment
Share on other sites

@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...

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.