Multiple rows in table with auto_increment column have save value in that col! How?

Saeven

I have hold of a system where I need to insert multiple rows into a table that has an 'id' column as:

| id         | int(10) unsigned |      | PRI | NULL    | auto_increment |

and that has several rows with same ID and with different values in the other different columns.

ex.

id       value1          value2
1            0                    0
1            0                    1
1            3                    4
2            4                    5
2            5                    6

How can I replicate this? How is the insert syntax done? I've tried doing an insert with several lines like:

INSERT INTO table (value1, value2) VALUES (0,0), (1,1);

But it simply increments Id for each of these. How can I get those inserted items to have same ID values even though the col is auto increment?

Looking forward to your replies!
Saeven

LordShryku

You can't. It will auto increment. Change it to int, non incrementing. Also, may want to change the primary key on id. Primary Key specifies a unique field.

Saeven

Thanks for the feedback - I've managed to get it with a bit of poking however.

Insert the first, and then perform LAST_INSERT_ID() to get the last insert and use replace statements explicitly specifying the ID and you'll get all of your columns with matching ID counts.

Cheers tho.
A.