0

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.

Community
  • 1
  • 1
Tim
  • 37
  • 2
  • 9

2 Answers2

1

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
Community
  • 1
  • 1
Andrew
  • 1,858
  • 13
  • 15
  • Thank you for your feedback! I appreciate your time. If I add a null to the insert statement where the appropriate column value would be, I get `Data too long for column 'taxiGroup' at row 1`, which is the column just previous to the PK. Also, I originally tried using `values`, but it kept throwing errors until I took that out. Any other thoughts? – Tim May 07 '16 at 05:17
  • Can you show full query? It can be helpful to solve your problem. – Andrew May 07 '16 at 05:23
  • That's weird. I deleted everything in the table and tried again, and everything ran without issue (adding the `null`, as you suggested). Weird. But thank you! Much appreciated. That's definitely counter-intuitive since the PK isn't nullable. – Tim May 07 '16 at 05:25
0

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?

Community
  • 1
  • 1
Alex_Keshav
  • 1
  • 2
  • Thank you very much for your input. I very much appreciate your time. I understand the difference now, but how do I find out if the db allows mutable PKs? – Tim May 07 '16 at 05:24