CODEWITHSUNDEEP
mysql
user6757781
user6757781

Reputation:

In MySQL, how to increment a Primary Key in a INSERT statement

I want to manually add another row in my table CUSTOMER while incrementing the CustomerID attributes.

This is how my CUSTOMER table is structured :

 ------------------------------------------------------
 | CustomerID          |   FirstName  |    LastName   |
 ------------------------------------------------------
 | INTEGER PRIMARY KEY |   CHAR(15)   |    CHAR(15)   |
 ------------------------------------------------------

In this table, I already have a row with CustomerID = 1, FirstName = Jeremhia, LastName = Cutecut. I want to add another row in this table without specifying the number in column CustomerID, in other words, I want to increment it.

This is what I did so far and it is not working :

INSERT INTO CUSTOMER (CustomerID, FirstName, LastName)
      VALUES (:vcustid, 'Abe', 'Lincoln');

I suspect that the variable vcustid is not working in phpMyAdmin, is there a similar variable or any other way to increment it so I got this following results in my table?

------------------------------------------------------
| CustomerID          |   FirstName  |    LastName   |
------------------------------------------------------
|         1           |   Jeremhia   |    Cutecut    |
------------------------------------------------------
|         2           |   Abe        |    Lincoln    |
------------------------------------------------------

Upvotes: 0

Views: 360

Answers (2)

Chris
Chris

Reputation: 120

You can also add the property AUTO_INCREMENT to the field to let sql handle the issue. you would then only pass the values

ALTER TABLE CUSTOMER MODIFY COLUMN CustomerID INT auto_increment

INSERT INTO CUSTOMER (CustomerID, FirstName, LastName)
      VALUES ('Abe', 'Lincoln');

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Create your table with AUTO_INCREMENT

CREATE TABLE CUSTOMER (
     CustomerID  INT NOT NULL AUTO_INCREMENT,
     FirstName  CHAR(30) NOT NULL,
     LastName  CHAR(30) NOT NULL
     PRIMARY KEY (CustomerID)
);

And let the db handle the PK

INSERT INTO CUSTOMER (FirstName, LastName)
       VALUES ('Abe', 'Lincoln');

Upvotes: 4

Related Questions