CODEWITHSUNDEEP
mysqlsql
user1227997
user1227997

Reputation: 11

Insert with self generated primary key

I am trying to perform an INSERT using a SELECT to generate a primary key

Table Employee:

ID(PK)   NAME
1        John
2        Doo
3        James

Table Friends:

ID(PK)   FriendName

Now I want to insert all Employees into the Friends and self generate the PK.

I tried:

INSERT INTO Friends (ID, FriendName) 
   SELECT  (SELECT max(ID)+1 FROM Friends) as ID
           , Name as FriendName 
   FROM    Employee

For the first record that works fine. Then I get a duplicate key error from MySQL.

How I can do this (Select max(ID)+1 from Friends) once per row? If that is not the correct approach, how should I structure my INSERT statement?

Thanks

Upvotes: 1

Views: 1060

Answers (1)

aF.
aF.

Reputation: 66757

If you want a key that is always incrementing check AUTO_INCREMENT.

Then you can do the insert like this:

INSERT INTO Friends (FriendName)
select Name as FriendName FROM Employee

Upvotes: 5

Related Questions