CODEWITHSUNDEEP
mysqlsqllast-insert-id
Ashish
Ashish

Reputation: 14707

issue with insert query in mysql

I have following query :

insert into A select last_insert_id(),B.id,C.name,C.address,
from sample_table C join other_table B on B.phoneNumber=C.phoneNumber;

I am getting duplicate primary key value =1 error ( which should be generated by last_insert_id() ). Here is the structure of tables 

A
id|phoneNumber|name|address
---------------------------

B
id|phoneNumber|email
--------------------

C
id|phoneNumber|name|address
---------------------------

Could someone please help me why last_insert_id() is always returning 1.

More Info: id field in table A,B and C are auto_increamented.

Upvotes: 0

Views: 36

Answers (2)

Ryan Nigro
Ryan Nigro

Reputation: 4619

If you're running this multiple times, did you mean to insert last_insert_id() + 1?

If you're not running this multiple times, then it sounds like table A already has a PK of 1, and you need to pick a different value for a.id or update the existing row.

Also, last_insert_id() returns the last inserted primary key for an auto-increment column. If A.id IS NOT an auto-increment column, then last_insert_id will not change value on inserts to A. If A.id IS an auto-increment column, then you don't need to include it in your insert anyways.

Edit 2: See below

insert into A 
select null, B.phonenumber,C.name,C.address,
from sample_table C 
join other_table B 
  on B.phoneNumber=C.phoneNumber;

Upvotes: 1

Jason Heo
Jason Heo

Reputation: 10246

as you know LAST_INSERT_ID() for AUTO_INCREMENTed column's inserted value.and NULL for AUTO_INCREMENT forces generating new value. what if you use NULL rather than last_insert_id():

INSERT INTO A 
SELECT NULL, B.id,C.name,C.address,
FROM sample_table C JOIN other_table B ON B.phoneNumber=C.phoneNumber;

Is there any reason you must use last_insert_id()? or just question?

Upvotes: 1

Related Questions