如果表中已有唯一键，如何强制插入到具有唯一键的表中？

发布于 2024-12-13 22:59:20 字数 329 浏览 2 评论 0原文

我有一个表：

CREATE TABLE Students (studentId TEXT PRIMARY KEY, name TEXT);

我想向表中插入记录，如果我插入一个学生两次我想要第二个插入覆盖（更新）第一条记录。

INSERT INTO Students (StudentId, name) VALUES ('123', 'Jones');
INSERT INTO Students (StudentId, name) VALUES ('123', 'Jonas');

这样做的最佳方法是什么？

原文

I have a table:

CREATE TABLE Students (studentId TEXT PRIMARY KEY, name TEXT);

I want to insert records into the table, if I insert a student twice I want
the second insert to override(update) the first record.

INSERT INTO Students (StudentId, name) VALUES ('123', 'Jones');
INSERT INTO Students (StudentId, name) VALUES ('123', 'Jonas');

What's the best way of doing this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

舂唻埖巳落 2024-12-20 22:59:20

尝试替换：

REPLACE INTO Students (StudentId, name) VALUES ('123', 'Jonas');

REPLACE 的工作方式与 INSERT 完全相同，只不过如果表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，则在插入新行之前会删除旧行。

Try REPLACE:

REPLACE INTO Students (StudentId, name) VALUES ('123', 'Jonas');

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

回复收藏 0 原文

ぶ宁プ宁ぶ 2024-12-20 22:59:20

您还可以使用 INSERT 。 .. ON DUPLICATE KEY UPDATE 语法：

INSERT INTO Students
    (StudentId, name) 
  VALUES ('123', 'Jones')
ON DUPLICATE KEY UPDATE
  name = VALUES(name) ;

请参阅此答案：insert-ignore-vs-insert-on-duplicate-key-update 了解 REPLACE、INSERT ... ON DUPLICATE KEY UPDATE 和 INSERT IGNORE 之间的差异。

但请告诉我们 studentId TEXT PRIMARY KEY 是一个拼写错误。你真的有一个主键 TEXT 数据类型？名称 (studentId) 表明它可能是一个简单的 INT 或 INT AUTO_INCRMENT。

You can also use the INSERT ... ON DUPLICATE KEY UPDATE syntax:

INSERT INTO Students
    (StudentId, name) 
  VALUES ('123', 'Jones')
ON DUPLICATE KEY UPDATE
  name = VALUES(name) ;

See this answer: insert-ignore-vs-insert-on-duplicate-key-update for differences between REPLACE, INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE.

But please, tell us that the studentId TEXT PRIMARY KEY is a typo. Do you really have a Primary Key that is TEXT datatype? The name (studentId) suggests that it could be a simple INT or INT AUTO_INCREMENT.

回复收藏 0 原文