CODEWITHSUNDEEP
mysql
Dragonspell
Dragonspell

Reputation: 347

MySQL INSERT INTO SELECT into a table with AUTO_INCREMENT

Consider the following table:

CREATE TABLE `FooAttachments` (
 `AttachmentId` bigint(20) NOT NULL AUTO_INCREMENT,
 `FooId` bigint(20) NOT NULL,
 `AttachmentPath` varchar(100) NOT NULL,
 PRIMARY KEY (`AttachmentId`),
 KEY `IX_FooAttachment_FooBase` (`FooId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf16

If I insert a row into this table using the following statement, a row is created and the AI field has a value generated.

INSERT INTO FooAttachments (FooId, AttachmentPath) VALUES (1, 'test path');

However, when I try to use an INSERT INTO SELECT statement as follows, I get an error:

INSERT INTO FooAttachments
    SELECT 1 AS FooId, 'test path' as AttachmentPath;

The error I get is: #1136 - Column count doesn't match value count at row 1

I understand that I'm not specifying the AttachmentId column, but I'm not doing so in the INSERT INTO ... VALUES statement either and that one works. More to the point, I don't want to specify a value for that column, it is supposed to be auto-generated.

How should I write this second query?

Upvotes: 3

Views: 7712

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

You must name the columns 

INSERT INTO FooAttachments (FooId, AttachmentPath)
SELECT 1 AS FooId, 'test path' as AttachmentPath;

Upvotes: 5

staticsan
staticsan

Reputation: 30565

The way to trigger an auto-increment is to insert a null. Like this:

INSERT INTO FooAttachments
SELECT NULL AS AttachmentID, 1 As FooId, 'test path' as AttachmentPath;

Upvotes: 2

Related Questions