[SOLVED] INSERT INTO QUERY

[Kathy]

I'm trying to insert multiple values into one field in my table using the following statement:

INSERT INTO table name (field)
        VALUES ('type','type','type',type')

I'm getting the following error if I do this though:

#1136 - Column count doesn't match value count at row 1

Can anybody give me some advice?

[kickstart]

Hi

 

Multiple rows? If so:-

 

INSERT INTO table name (field)
        VALUES ('type'),('type'),('type'),('type')

 

All the best

 

Keith

[Kathy]

yes, that's what I'm trying to do,

tried your suggestion, got the following

error:

#1062 - Duplicate entry '0' for key 1

[kickstart]

Hi

 

The error you are getting suggests that the field you are specifying is a unique key and you are trying to insert duplicate values for it.

 

What is the actual SQL you are using and what are the fields?

 

All the best

 

Keith

[Kathy]

Here is my query:

 

INSERT INTO categories (Category) VALUES ('Bottling Services'),('Brush Cutters'),('Building Construction/Supplies'),('Bulk Wine & Brandy Traders')

 

Is this wrong?

???

[kickstart]

Hi

 

Depends on the layout of the table categories.

 

My guess is that there is another column in the table set as the primary key but not set as an autoincrement.

 

ie, you have something like :-

 

CREATE TABLE `categories` (
  `Id` int(11) NOT NULL,
  `Category` varchar(50) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

and need something like:-

 

CREATE TABLE `categories` (
  `Id` int(11) NOT NULL auto_increment,
  `Category` varchar(50) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

All the best

 

Keith

[Kathy]

That did it! Thanks! 

[Kathy]

Just tried the following:

INSERT INTO categories (Category) VALUES (‘Chemicals & Refining Agents’),(‘CIP Systems’),(‘Cleaning’),(‘Equipment & Services’)

I get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Agents’),(‘CIP Systems’),(‘Cleaning’),(‘Equipment & Services’)' at line 1

why is this?

[kickstart]

Hi

 

Wrong type of quotes? Tried that with normal single quotes and it works fine.

 

All the best

 

Keith