Simple ON DUPLICATE KEY UPDATE?

I'm trying to store a one-to-many relationship in my mySQL database and I want to avoid duplicate entries if at all possible.

I read on the internet and saw that 'ON DUPLICATE KEY UPDATE' is an option. Would this work for my situation?

CODE:

function insert_options($uid, $array) {
    if(!is_array($array)) {
        return false;
    }
    $db = db_connect();
    foreach($array as $a) {
        $sql = 'INSERT INTO newsletter_coupon_codes_options (uid, option_name, value) VALUES (?, ?, ?)';
        $stmt = $db->prepare($sql);
        if($stmt === false) {
            echo "Prepare failed: (" . $db->errno . ") " . $db->error;
        }
        $stmt->bind_param('iss', $uid, $a, $value);
        $value = '1';
        $stmt->execute();
    }
}

TABLE

UID(INT11), OPTION_NAME(varchar(255)), VALUE(INT(11))

I tried to add ON DUPLICATE KEY UPDATE value = 1 to the $sql statement, but it didn't seem to do anything. Basically I want to make sure when I'm inserting the data if the uid and option_name exist, it just sets the value to 1 and doesn't make a new entry.

I do not have a unique key set up, only a primary key on uid . I wasn't sure what to make unique. I can't make the uid unique, as there will be multiple entries per uid . I also cannot make the option_name unique as there will be the same option_name for multiple uid 's

What is the best way to accomplish this?

---

Change your query to

INSERT INTO newsletter_coupon_codes_options (uid, option_name, value) 
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE value = VALUES(value)

Here is SQLFiddle demo

And make sure that you have a UNIQIE or PRIMARY KEY constraint on (uid, option_name) , eg

ALTER TABLE newsletter_coupon_codes_options 
  ADD UNIQUE (uid, option_name);

---

请更改您的查询，并使用mysqli_query函数执行此类查询并使用mysqli_real_escape_string函数来避免SQL注入

$sql = 'INSERT INTO newsletter_coupon_codes_options 
     (uid, option_name, value) VALUES ("'.$uid.'", "'.$option_name.'", "'.$value.'")
    ON DUPLICATE KEY UPDATE  value = "'.$value.'"';

链接地址: http://www.djcxy.com/p/66758.html

上一篇: Sql更新：重复键错误后继续更新（事务

下一篇: 简单的重复密钥更新？