Duplicate a row in SQL?

Question

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

Answer 1

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.