Simple SQL, Setting default value for multiple columns in a table

Question

So I'm doing homework for a class and I have been stuck on this problem for days, apparently I'm not as good at google as I need to be.

Here it is: "Change the StoreReps table so that NULL values can’t be entered in the first and last name columns."

My Code (does not work):

    Alter Table StoreRep Modify lastname Not Null, Modify firstname Not Null;

My Code (does work but I need to be able to change both columns at the same time):

    Alter Table StoreRep Modify lastname Not Null;

Please add appropriate tags for SQL dialect you are using. From syntax I assume your RDMS is Oracle — a1ex07
– a1ex07, Commented May 10, 2013 at 14:34

Linus Caldwell · Accepted Answer · 2013-05-10 14:33:05Z

2

If you're using MySQL, you also need to specify the type:

alter table StoreRep
modify firstname varchar(50) not null,
modify lastname varchar(50) not null;

See this demo.

answered May 10, 2013 at 14:33

Linus Caldwell

11.1k12 gold badges48 silver badges61 bronze badges

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

1 Comment

a1ex07 Over a year ago

That all depends on dialect. Ravi Thapliyal's answer is good for Oracle, yours for MySql.

Ravi K Thapliyal · Accepted Answer · 2013-05-10 14:27:58Z

1

Alter Table StoreRep Modify (lastname Not Null, firstname Not Null);

answered May 10, 2013 at 14:27

Ravi K Thapliyal

51.9k9 gold badges80 silver badges90 bronze badges

1 Comment

user2238127 Over a year ago

Thanks! This worked. I am using Oracle for my class. I thought I tried this, but apparently I was missing the parenthesis.

Timothy G. · Accepted Answer · 2021-08-22 13:34:59Z

0

You can use following query which can alter default value for multiple columns using single run:

     ALTER TABLE `wp_posts`  CHANGE `post_date` `post_date` DATETIME NOT NULL 
     DEFAULT CURRENT_TIMESTAMP, CHANGE `post_date_gmt` `post_date_gmt` 
     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CHANGE `post_modified` 
    `post_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CHANGE 
    `post_modified_gmt` `post_modified_gmt` DATETIME NOT NULL DEFAULT 
     CURRENT_TIMESTAMP;

edited Aug 22, 2021 at 13:34

Timothy G.

9,8597 gold badges50 silver badges70 bronze badges

answered Aug 17, 2021 at 16:26

Sumit Jangir

811 silver badge4 bronze badges

Collectives™ on Stack Overflow

Simple SQL, Setting default value for multiple columns in a table

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related