Insert rows using mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tat
Forum Newbie
Posts: 11
Joined: Mon Feb 02, 2009 10:56 am

Insert rows using mysql

Post 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!
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Insert rows using mysql

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Insert rows using mysql

Post by VladSun »

Make your primary key field:
[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
tat
Forum Newbie
Posts: 11
Joined: Mon Feb 02, 2009 10:56 am

Re: Insert rows using mysql

Post 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'
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Insert rows using mysql

Post by Skoalbasher »

What Vlad said. You have to make your primary key auto increment.
tat
Forum Newbie
Posts: 11
Joined: Mon Feb 02, 2009 10:56 am

Re: Insert rows using mysql

Post by tat »

sorry how do I set as not null auto increment? can you please be more specific?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Insert rows using mysql

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
tat
Forum Newbie
Posts: 11
Joined: Mon Feb 02, 2009 10:56 am

Re: Insert rows using mysql

Post by tat »

thanks a zillion...
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: Insert rows using mysql

Post 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?
Post Reply