CODEWITHSUNDEEP
mysqlsql
Tim
Tim

Reputation: 37

Auto-incrementing row ID

I'm trying to update values already stored in a table, and I've implemented an auto-incrementing primary key column so that I can reference specific rows by number (as recommended here).

Using...

ALTER TABLE taxipassengers ADD COLUMN rid INT NOT NULL AUTO_INCREMENT PRIMARY KEY

The problem I'm running into though is that now I'm getting Column count doesn't match value count at row 1 when I insert the same data as before. It's like it wants me to give it a value for the PK. If I delete the column with the PK, the error goes away, and I'm back to square one.

Am I missing something?

EDIT: Here's the insert statement

INSERT INTO taxipassengers SELECT a.post_date, b.vendor_name, c.lastName, d.firstName, null as taxiGroup
FROM (select ID,post_date from wp_posts where post_type = 'shop_order') a, 
(SELECT order_id,vendor_name FROM wp_wcpv_commissions) b, 
(SELECT post_id,meta_value as lastName FROM wp_postmeta where meta_key ='_billing_last_name') c, 
(SELECT post_id,meta_value as firstName FROM wp_postmeta WHERE meta_key ='_billing_first_name') d 
WHERE a.ID = b.order_id and b.order_id=c.post_id and c.post_id = d.post_id;

Mind you, the insert statement worked before implementing the PK column, and it still works if I remove the PK column.

Upvotes: 0

Views: 937

Answers (2)

Andrew
Andrew

Reputation: 1866

Possibly you are using this syntax to insert rows

INSERT INTO mytable VALUES (1, 'abc', 'def');

INSERT syntax from MySQL manual

The columns for which the statement provides values can be specified as follows:

  • If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.

You must add new column to your INSERT query. For autoincrement column NULL can be inserted to generate new value. And your column will be added last by default (if you don't use AFTER in ALTER TABLE).

To add a column at a specific position within a table row, use FIRST or AFTER col_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.

So, now your INSERT must look like this:

INSERT INTO mytable VALUES (1, 'abc', 'def', NULL); -- use NULL for autoincrement

INSERT INTO mytable (col1, col2, col3) VALUES (1, 'abc', 'def'); -- or add column names

Upvotes: 1

Alex_Keshav
Alex_Keshav

Reputation: 1

First check if your database allows mutable PK, or prefers stable PK .

Please read through below articles, I am sure you will get what's going wrong. http://rogersaccessblog.blogspot.in/2008/12/what-is-primary-key.html

First, the value in the primary key cannot be duplicated

Can we update primary key values of a table?

Upvotes: 0

Related Questions