Guys,
I am tring to insert rows into an empty three columns table in mysql. Only column 3 should be populated with 705 rows, column 2 should remain null and column 1 is the primary key. when I use the below insert syntax
INSERT INTO table table_name
(col3)
VALUES (v1),(v2),(v3),....
ON DUPLICATE KEY UPDATE primarykeycol=primarykeycol+1
only to 2 rows get inserted. What do I need to do to have all 705 inserted and have a primary key value assigned to all of them?
Thanks a lot!
Insert rows using mysql
Moderator: General Moderators
- Skoalbasher
- Forum Contributor
- Posts: 147
- Joined: Thu Feb 07, 2008 8:09 pm
Re: Insert rows using mysql
Are you updating rows or creating new ones?tat wrote:Guys,
I am tring to insert rows into an empty three columns table in mysql. Only column 3 should be populated with 705 rows, column 2 should remain null and column 1 is the primary key. when I use the below insert syntax
INSERT INTO table table_name
(col3)
VALUES (v1),(v2),(v3),....
ON DUPLICATE KEY UPDATE primarykeycol=primarykeycol+1
only to 2 rows get inserted. What do I need to do to have all 705 inserted and have a primary key value assigned to all of them?
Thanks a lot!
Re: Insert rows using mysql
Make your primary key field:
[sql]NOT NULL AUTO_INCREMENT[/sql]
and put NULL for its value when inserting.
[sql]NOT NULL AUTO_INCREMENT[/sql]
and put NULL for its value when inserting.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Insert rows using mysql
I am creating new rows .
when I use the insert statement:
insert into perfume
(col3)
values (v1),(v2),...
I get an error message:
Duplicate entry '0' for key 'PRIMARY'
when I use the insert statement:
insert into perfume
(col3)
values (v1),(v2),...
I get an error message:
Duplicate entry '0' for key 'PRIMARY'
- Skoalbasher
- Forum Contributor
- Posts: 147
- Joined: Thu Feb 07, 2008 8:09 pm
Re: Insert rows using mysql
What Vlad said. You have to make your primary key auto increment.
Re: Insert rows using mysql
sorry how do I set as not null auto increment? can you please be more specific?
Re: Insert rows using mysql
[sql]CREATE TABLE `table` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, ...[/sql]
http://dev.mysql.com/doc/refman/5.0/en/ ... ement.html
http://dev.mysql.com/doc/refman/5.0/en/ ... ement.html
There are 10 types of people in this world, those who understand binary and those who don't
Re: Insert rows using mysql
thanks a zillion...
- Skoalbasher
- Forum Contributor
- Posts: 147
- Joined: Thu Feb 07, 2008 8:09 pm
Re: Insert rows using mysql
This will prevent you from duplicating the key. And you won't have to check for duplicates anymore! I think your SQL is a little off though.
You said you have 720 rows you want to put in. You are using a for or while loop or something I imagine?
You said you have 720 rows you want to put in. You are using a for or while loop or something I imagine?