CODEWITHSUNDEEP
mysqlsql
shelbypereira
shelbypereira

Reputation: 2245

MySQL insert row with only default values

I would like to insert a row with only the default values (which I will then update later since I need the ID autoincremented field)

This works in SQL Server (How to insert a record with only default values?) 

insert into myTable DEFAULT  VALUES;

But how can I accomplish this in MySQL:

I also tried:

insert into myTable;

which fails. I know I can work around with the standard insert syntax, but there are a lot of columns in my table so a simple syntax if it exists would be helpful.

Upvotes: 5

Views: 4957

Answers (1)

Blag
Blag

Reputation: 5894

This will do it :

INSERT INTO `myTable` (`id`)
VALUES 
    (null),
    (null);
-- or 
INSERT INTO `myTable` () 
VALUES();

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`id` int AUTO_INCREMENT PRIMARY KEY, `title` varchar(5) DEFAULT '***')
;

INSERT INTO Table1
    (`id`, `title`)
VALUES
    (1, 'hi'),
    (2, 'hello')
;

INSERT INTO Table1
    (`id`)
VALUES
    (null),
    (null)
;
INSERT INTO Table1 () VALUES();

Query 1:

SELECT * from Table1

Results:

| id | title |
|----|-------|
|  1 |    hi |
|  2 | hello |
|  3 |   *** |
|  4 |   *** |
|  5 |   *** |

Upvotes: 10

Related Questions