Reputation: 347
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
Reputation: 44363
You must name the columns
INSERT INTO FooAttachments (FooId, AttachmentPath)
SELECT 1 AS FooId, 'test path' as AttachmentPath;
Upvotes: 5
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