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 stackoverflow.com/questions/11801911/…. Please use the search feature before asking questions. This is easily found on Stack Overflow, and the rest of the internet. — Jonathon Reinhart
– Jonathon Reinhart, Commented Sep 18, 2013 at 5:14
@JonathonReinhart It's not a duplicate of that at all. He isn't asking to insert multiple rows. — Fluffeh
– Fluffeh, Commented Sep 18, 2013 at 8:04
@JonathonReinhart I agree with Fluffeh; I don't see how that's a duplicate of this.' — Andrew Barber
– Andrew Barber, Commented Sep 18, 2013 at 15:23
Okay, but it shows how to insert into a single column. Wasn't that the point? — Jonathon Reinhart
– Jonathon Reinhart, Commented Sep 18, 2013 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
– Vincent, Commented Sep 19, 2013 at 1:46

cha · Accepted Answer · 2013-09-18 05:13:54Z

2

Set Default for item:

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

answered Sep 18, 2013 at 5:13

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vincent Over a year ago

so using the DEFAULT function will make the value of item as 30 each time i insert a new product name?

Sal00m Over a year ago

Exactly if you don't set a value for this column the default value will be inserted

Fluffeh · Accepted Answer · 2013-09-18 08:03:30Z

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

Fluffeh · Accepted Answer · 2013-09-18 08:08:19Z

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)

Marc Delisle · Accepted Answer · 2013-09-18 12:23:05Z

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.

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Linked

Related