Im using mysql database. When i add some sample data inside my database when i want to return the last insert id as follow:
SELECT DISTINCT LAST_INSERT_ID() FROM facturatiedatabase.tblinvoice;
It returns customersID instead.
This the table i'm using.
CREATE TABLE tblInvoice (
invoiceID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
customerID INT,
totalPriceExcl decimal(65,2),
vat decimal(65,2),
totalPriceIncl decimal(65,2),
conditionState VARCHAR(100) NOT NULL DEFAULT 'In progress' ,
createDate Date,
invoiceNumber int ,
CONSTRAINT FK_customerID_Invoice FOREIGN KEY (customerID) REFERENCES tblCustomers(customerID)
)ENGINE=InnoDB;
Is there something wrong with my syntax or is this a common problem? And is there a workaround ?
You're using it wrongly.
From the docs:
LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:
Note the example doesn't query a table. It's based on the current connection.
mysql> SELECT LAST_INSERT_ID();
-> 195
If you do an insert into tblInvoice then run SELECT LAST_INSERT_ID() you'll get back the invoiceID.
So a working example would be:
INSERT INTO tblInvoice (columns) VALUES (data);
SELECT LAST_INSERT_ID(); -- The invoiceID generated from above insert
If what you really want is the last created invoice ID, try:
SELECT MAX(invoiceID) FROM tblInvoice
Not perfect, but should be enough for your needs.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments