0

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.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Ashish
  • 14,295
  • 21
  • 82
  • 127

2 Answers2

1

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;
Ryan Nigro
  • 4,389
  • 2
  • 17
  • 23
  • it does not matter because then it will keep returning 2 – Ashish Nov 27 '13 at 00:16
  • 1
    I disagree. The first time the script runs, last_insert_id() will return 1. The next time, it will return 2, because that will be the last inserted PK value. – Ryan Nigro Nov 27 '13 at 00:17
  • and that is what I am trying to say sir that no matter how many time it run it is returning 1 always. – Ashish Nov 27 '13 at 00:17
  • @Ashish With `last_insert_id()` (not `+1`), then it will always return 1, yes. – kba Nov 27 '13 at 00:19
  • Also, the value of last_insert_id() is always constant in a single statement with multiple inserts. In other words, if you copy and paste the same insert 5 times and execute all 5 inserts in a single statement, then you'll get the same value for last_insert_id() for all instances of that insert within your statement, which will throw an error. In stored procedures, the behavior is different, and last_insert_id() will always be the last inserted auto_increment id. – Ryan Nigro Nov 27 '13 at 00:21
  • even for +1 it is giving same error Duplicate entry '1' for key 'PRIMARY' – Ashish Nov 27 '13 at 00:21
  • I have seen your answer first the column is auto increment, second how insert is going to decide not to put phonenumber in ID and that is what happening if I remove last_insert_id() – Ashish Nov 27 '13 at 00:26
  • @Ashish Are you inserting the `last_insert_id()` just becuase you don't want `B.id` to be stored as `A.id`, but instead you want `B.id` stored as `A.phoneNumber`? If so, look at the [syntax of `INSERT`](http://dev.mysql.com/doc/refman/5.6/en/insert.html), specifically the `VALUES` part. – kba Nov 27 '13 at 00:29
1

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?

Jason Heo
  • 9,956
  • 2
  • 36
  • 64