在 SQL 中复制一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/615143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Duplicate a row in SQL?
提问by MetaGuru
OK I have a table that has two columns, userID and courseID. It is used to assign training courses to a user. It looks like this:
好的，我有一个包含两列、userID 和 courseID 的表。它用于向用户分配培训课程。它看起来像这样：
userid courseid
0 1
0 3
0 6
1 1
1 4
1 5
so user 0 is assigned to courses 1,3,6 and user 1 is assigned to 1, 4 5
所以用户 0 被分配到课程 1,3,6，用户 1 被分配到 1, 4 5
anyways I need to take every user that is assigned to 6 and create a new row that has that userid and courseid 11, basically assigning every user who is currently assigned to 6 to also be assigned to 11
无论如何，我需要获取分配给 6 的每个用户并创建一个具有该用户 ID 和课程 ID 11 的新行，基本上将当前分配给 6 的每个用户也分配给 11
for some reason (I did not create this database) both rows are marked as primary keys, and some statements I have tried have thrown an error because of this, what the heck is the deal?
出于某种原因（我没有创建这个数据库）两行都被标记为主键，我尝试过的一些语句因此引发了错误，这到底是怎么回事？
oh maybe it is because there are a few users that are already assigned to 11 so it is choking on those maybe?
哦，也许是因为有一些用户已经被分配到 11，所以它可能被这些用户所窒息？
please help
请帮忙
回答by Mark Brittingham
Insert Into TableName (userID, courseID)
Select userID, 11 From TableName Where courseID=6;
Also, I'm a bit confused by your comment that both are primary keys. Both rows can be partof the primary key or both can be Unique keys but they cannot both be a primary key. As far as errors go, it is probably because the query tried to insert rows that were duplicates of already existing rows. To eliminate this possibility you could do this:
另外，我对您的评论感到有些困惑，即两者都是主键。两行都可以是主键的一部分，也可以都是唯一键，但不能都是主键。就错误而言，这可能是因为查询试图插入与现有行重复的行。要消除这种可能性，您可以这样做：
Insert Into TableName (userID, courseID)
Select userID, 11 From TableName Where courseID=6
AND (userID not in (Select userID From TableName Where courseID=11))
Depending on your database this could work too:
根据您的数据库，这也可以工作：
INSERT OR IGNORE INTO TableName (userID, courseID)
SELECT userID, 11 FROM TableName WHERE courseID=6;
Anyway, there you go.
无论如何，你去吧。
回答by Garry Shutler
insert into TableName (userId, courseId)
select userId, 11
from TableName
where courseId = 6
and not exists (
select 1
from TableName nested
where nested.userId = TableName.UserId
and nested.courseId = 11
)
Selects all users that are assigned to courseId 6 but are not yet assigned to courseId 11 and inserts a new record into the table for them for courseId 11.
选择已分配给 courseId 6 但尚未分配给 courseId 11 的所有用户，并为 courseId 11 将新记录插入到表中。
回答by achinda99
This should help:
这应该有帮助：
INSERT
INTO [table]
(
userid,
courseid
)
SELECT userid,
11
FROM [table]
WHERE courseid = 6
AND userid NOT IN
(SELECT userid
FROM [table]
WHERE courseid = 11
);
This will select all users in course 6 not in course 11 and add them with course 11 to the table.
这将选择课程 6 中而不是课程 11 中的所有用户，并将他们与课程 11 一起添加到表中。
