Page 1 of 1

Insert rows using mysql

Posted: Wed Feb 04, 2009 10:47 am
by tat
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

Posted: Wed Feb 04, 2009 10:53 am
by Skoalbasher
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!
Are you updating rows or creating new ones?

Re: Insert rows using mysql

Posted: Wed Feb 04, 2009 10:58 am
by VladSun
Make your primary key field:
[sql]NOT NULL AUTO_INCREMENT[/sql]
and put NULL for its value when inserting.

Re: Insert rows using mysql

Posted: Wed Feb 04, 2009 11:02 am
by tat
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'

Re: Insert rows using mysql

Posted: Wed Feb 04, 2009 11:06 am
by Skoalbasher
What Vlad said. You have to make your primary key auto increment.

Re: Insert rows using mysql

Posted: Wed Feb 04, 2009 11:07 am
by tat
sorry how do I set as not null auto increment? can you please be more specific?

Re: Insert rows using mysql

Posted: Wed Feb 04, 2009 11:11 am
by VladSun
[sql]CREATE TABLE `table` (  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,   ...[/sql]

http://dev.mysql.com/doc/refman/5.0/en/ ... ement.html

Re: Insert rows using mysql

Posted: Wed Feb 04, 2009 11:11 am
by tat
thanks a zillion...

Re: Insert rows using mysql

Posted: Wed Feb 04, 2009 11:18 am
by Skoalbasher
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?