批量插入/更新mysql

Question

假设我想要执行下面的mysql，表中已经有几条记录，可能有不止一条记录已经存在。如果此记录(21,'Helen','a')和(21,'Hui Ling','b')已存在于表中，并且我希望使用where条件执行插入和更新，如..那么我的where name = "Helen" and grade="a"查询部分应该是什么呢？

INSERT INTO students (points,name,grade)
    VALUES
    (21,'Helen','b'),
    (21,'Helen','c' ),
    (21,'Samia','a'),
    (21,'Hui Ling','b'),
    (21,'Yumie','a'),
    (21,'Helen','a')
ON DUPLICATE KEY UPDATE
    grade = VALUES('a'),

Answer 1

如果我理解正确的话，您希望将grade设置为传入的内容。您想要使用VALUES()结构：

INSERT INTO students (points, name, grade)
    VALUES
        (21,'Helen','b'),
        (21,'Helen','c' ),
        (21,'Samia','a'),
        (21,'Hui Ling','b'),
        (21,'Yumie','a'),
        (21,'Helen','a')
    ON DUPLICATE KEY UPDATE grade = VALUES(grade);

这将为表任意选择一个值。Here是一个SQL。

Answer 2

我不清楚你想要什么，但你几乎肯定需要看看你的钥匙，例如

MariaDB [sandbox]> drop table if exists students;
Query OK, 0 rows affected (0.17 sec)

MariaDB [sandbox]> create table students
    ->  (id int auto_increment primary key,points int,name varchar(20),grade varchar(1));
Query OK, 0 rows affected (0.19 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO students (points,name,grade)
    ->     VALUES
    ->     (21,'Helen','b'),
    ->     (21,'Helen','c' ),
    ->     (21,'Samia','a'),
    ->     (21,'Hui Ling','b'),
    ->     (21,'Yumie','a'),
    ->     (21,'Helen','a'),
    ->     (22,'Helen','z')
    -> ON DUPLICATE KEY UPDATE
    ->     grade = VALUES(grade);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from students;
+----+--------+----------+-------+
| id | points | name     | grade |
+----+--------+----------+-------+
|  1 |     21 | Helen    | b     |
|  2 |     21 | Helen    | c     |
|  3 |     21 | Samia    | a     |
|  4 |     21 | Hui Ling | b     |
|  5 |     21 | Yumie    | a     |
|  6 |     21 | Helen    | a     |
|  7 |     22 | Helen    | z     |
+----+--------+----------+-------+
7 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> truncate table students;
Query OK, 0 rows affected (0.23 sec)

MariaDB [sandbox]> alter table students
    -> add unique key studentsk1(name,points);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sandbox]> INSERT INTO students (points,name,grade)
    ->     VALUES
    ->     (21,'Helen','b'),
    ->     (21,'Helen','c' ),
    ->     (21,'Samia','a'),
    ->     (21,'Hui Ling','b'),
    ->     (21,'Yumie','a'),
    ->     (21,'Helen','a'),
    ->     (22,'Helen','z')
    -> ON DUPLICATE KEY UPDATE
    ->     grade = VALUES(grade);
Query OK, 9 rows affected (0.02 sec)
Records: 7  Duplicates: 2  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from students;
+----+--------+----------+-------+
| id | points | name     | grade |
+----+--------+----------+-------+
|  1 |     21 | Helen    | a     |
|  2 |     21 | Samia    | a     |
|  3 |     21 | Hui Ling | b     |
|  4 |     21 | Yumie    | a     |
|  5 |     22 | Helen    | z     |
+----+--------+----------+-------+
5 rows in set (0.00 sec)

请注意，在第一个插入中，海伦是重复的，因为没有惟一的键可供ON复制键测试。在alter table添加唯一键之后，下面的insert将检测重复的键，并按预期工作。