Duplicate a row in SQL?

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
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

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

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?

please help

---

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 part of 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.

---

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
           )

选择分配给courseId 6但尚未分配给courseId 11的所有用户，并为他们插入一条新记录给course11。

---

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.

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

上一篇: SQL，如何将表的所有行插入到另一个表中

下一篇: 在SQL中复制一行？