如果存在，插入到SQL表或更新记录中

I want to add a row to a database table, but if a row exists with the same unique key, I want to update the row.

我想向数据库表添加一行，但是如果存在具有相同唯一键的行，我想更新该行。

Here is my query:

这是我的查询:

$query = "INSERT INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark') 
ON DUPLICATE KEY UPDATE mark = VALUES($mark)";

But, it will keep on inserting new record. Let's say audit_section_id, form_details_subquestion_id, form_details_section_id is unique key. If audit_section_id, form_details_subquestion_id, form_details_section_id exists it will not insert new record into the database; while update the record.

但是，它将继续插入新的记录。假设audit_section_id, form_details_subquestion_id, form_details_section_id是唯一键。如果audit_section_id、form_details_subquestion_id、form_details_section_id存在，它将不会向数据库插入新记录;虽然更新记录。

I also tried this:

我也试过这样的:

REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_‌details_section_id`,`mark`)
VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark')

I not sure is that the for loop problem of my code.

我不确定我的代码的for循环问题。

    for ($i=0; $i < ($_POST['count']); $i++)
{
    $form_details_subquestion_id    = $_POST['form_details_subquestion_id'][$i];
    $form_details_section_id        = $_POST['form_details_section_id'][$i];
    $mark                           = $_POST['mark'][$i];
    $remark                         = $_POST['remark'][$i]; 


    //$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (' ".$_POST['id']." ', ' ".$_POST['name']." ', ' ".$_POST['email']." ')";    

    $query = "REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`)
    VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark')";
    $result = $db->query($query);
}

I'm using for loop to insert all the data.

我用for循环插入所有的数据。

Here is the output for echo $query;

这是echo $query的输出;

  REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','89','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','86','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','87','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','88','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','85','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','83','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','84','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','81','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','82','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','98','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','99','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','100','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','101','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','102','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','96','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','97','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','90','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','91','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','92','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','93','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','94','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','95','1','2')

SQL table

SQL表

   CREATE TABLE IF NOT EXISTS `audit_section_markrecord` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_section_id` int(10) unsigned NOT NULL,
  `form_details_subquestion_id` int(10) unsigned NOT NULL,
  `form_details_section_id` int(10) unsigned NOT NULL,
  `mark` decimal(5,2) unsigned NOT NULL,
  `dateofmodify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

1 个解决方案

#1

Edit after your last question edit:

在你最后一个问题之后编辑

you have in your table this:

你的桌子上有

`id` int(11) NOT NULL AUTO_INCREMENT,
....
 PRIMARY KEY (`id`)

Your id (PRIMARY KEY) is autoincremental and your question is:

您的id(主键)是自动递增的，您的问题是:

I want to add a row to a database table, but if a row exists with the same unique key, I want to update the row.

我想向数据库表添加一行，但是如果存在具有相同唯一键的行，我想更新该行。

With your querys your never will have the same id because you never set the id value in your query, your query is:

对于查询，您的id永远不会相同，因为您从未在查询中设置id值，您的查询是:

REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','95','1','2')

and if you don't set the id value in your query the database system autoincrement automatically the id column value, and you never will have to update a row.

如果您没有在查询中设置id值，那么数据库系统会自动地自动增加id列值，而且您永远都不需要更新一行。

This should be your query:

这应该是您的查询:

$query = "REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`)
    VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark')";

But the audit_section_id, form_details_subquestion_id and form_details_section_id column values must match those of an existing row for the row to be replaced; otherwise, a row is inserted.

但是audit_section_id、form_details_subquestion_id和form_details_section_id列值必须与要替换的行的现有行值相匹配;否则，将插入一行。

You can read more at:

你可以在:

http://dev.mysql.com/doc/refman/5.7/en/replace.html

http://dev.mysql.com/doc/refman/5.7/en/replace.html

EXAMPLE:

例子:

Consider the table created by the following CREATE TABLE statement:

考虑以下CREATE table语句创建的表:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

When you create this table and run the statements shown in the mysql client, the result is as follows:

当您创建这个表并运行mysql客户机中显示的语句时，结果如下:

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test; 
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

Now if you create a second table almost identical to the first, except that the primary key now covers 2 columns, as shown here (PRIMARY KEY (id, ts)):

现在，如果您创建的第二个表与第一个表几乎相同，除了主键现在包含2列，如下所示(主键(id, ts)):

CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);

When you run on test2 the same two REPLACE statements as we did on the original(first) test table, we obtain a different result:

当您在test2上运行与原始(第一个)测试表相同的两个替换语句时，我们会得到不同的结果:

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

This is due to the fact that, when run on test2, both the id and ts column values must match those of an existing row for the row to be replaced; otherwise, a row is inserted.

这是由于在test2上运行时，id和ts列值必须与要替换的行的现有行匹配;否则，将插入一行。

#1