INSERT in a single column, the rest of the column will have a default value

Question

How to insert a default value in all columns after inserting a value in a single column,

Example: I created a product table and the columns are id (autoincrement), product_name, and item.

CREATE TABLE product
(
    id int AUTOINCREMENT,
    product_name varchar(255),
    item int
);

How can I insert only the product_name and have the item automatically populated with the value 30?

insert into 
    product 
values (
    'burger'
)

and have the result be

id: product_name:  item: 
7   burger         30

Dup of http://stackoverflow.com/questions/11801911/insert-multiple-rows-into-single-column. Please use the search feature before asking questions. This is easily found on Stack Overflow, and the rest of the internet. — Jonathon Reinhart, Sep 18 '13 at 05:14
@JonathonReinhart It's not a duplicate of that at all. He isn't asking to insert multiple rows. — Fluffeh, Sep 18 '13 at 08:04
@JonathonReinhart I agree with Fluffeh; I don't see how that's a duplicate of this.' — Andrew Barber, Sep 18 '13 at 15:23
Okay, but it shows how to insert into a single column. Wasn't that the point? — Jonathon Reinhart, Sep 18 '13 at 20:30
@Jonathon Reinhart, my point here is after you created a table, if i insert in only single row, the other rows should have a default value. — Vincent, Sep 19 '13 at 01:46
@JonathonReinhart and your link doesnt answers my question. sorry — Vincent, Sep 19 '13 at 01:55

score 2 · Accepted Answer · answered Sep 18 '13 at 05:13

2

Set Default for item:

CREATE TABLE product ( id int AUTOINCREMENT, product_name varchar(255), item int DEFAULT 30);

answered Sep 18 '13 at 05:13

cha

10,301
1
18
26

so using the DEFAULT function will make the value of item as 30 each time i insert a new product name? – Vincent Sep 18 '13 at 05:39
Exactly if you don't set a value for this column the default value will be inserted – Sal00m Sep 18 '13 at 06:21

score 0 · Answer 2 · edited Sep 18 '13 at 08:03

0

CREATE TABLE product ( id int AUTOINCREMENT, product_name varchar(255), item int );

in mssql server use the following query:

ALTER TABLE product
ADD item int

CONSTRAINT DF_TestTable_NewCol DEFAULT 30 not null
GO

edited Sep 18 '13 at 08:03

Fluffeh

33,228
16
67
80

answered Sep 18 '13 at 05:39

The tag however specifically asks for MySQL :( – Fluffeh Sep 18 '13 at 08:03

score 0 · Answer 3 · answered Sep 18 '13 at 08:08

If your table has default or increment values, you can use either of the following syntax to insert the data and have it automatically fill the rest:

CREATE TABLE product
(
id int AUTOINCREMENT,
product_name varchar(255),
item int
);

You have an automatic value for ID, but no defaults set for either of the other two:

insert into product (product_name, item) values ('Burger', 30);

OR

insert into product values(null, 'Burger', 30)

Now, if you set your table to have default columns like @cha suggested, you can enter in even less info:

CREATE TABLE product
(
id int AUTOINCREMENT,
product_name varchar(255),
item int default 30
);

Allows you to:

insert into product (product_name) values ('Burger');

OR

insert into product values(null, 'Burger', null)

score 0 · Answer 4 · answered Sep 18 '13 at 12:23

0

Via phpMyAdmin, go to Structure for your table, click on Change for the item column. You can set the default value there by picking "As defined:" and entering the value.

answered Sep 18 '13 at 12:23

Marc Delisle

8,879
3
29
29

INSERT in a single column, the rest of the column will have a default value

4 Answers4