Incorrect syntax near the keyword 'SET' IN ALTER STATEMENT

Question

I HAVE FOLLOWING QUERY... THROUGH WHICH I WANT TO ALTER DEFAULT VALUE

ALTER TABLE MBR_MST2 ALTER COLUMN MBR_STS SET DEFAULT 1

don't know why it is showing an error Incorrect syntax near the keyword 'SET'.

@BlaZuRE - yep - forgot to switch the database type. Haven't had my coffee yet... :-) — Bob Jarvis - Слава Україні, Jul 28 '13 at 13:17

score 3 · Answer 1 · edited Feb 12 '16 at 19:31

3

The query as follows will sort it out. Refer the below handy code sample has been written by Pinal Dave

ALTER TABLE MBR_MST2 
   ADD CONSTRAINT MBR_MST2_CONST DEFAULT 1 FOR MBR_STS

edited Feb 12 '16 at 19:31

marc_s

answered Jul 28 '13 at 12:51

Smaug

You beat me by 47 seconds... Since i am new, should i delete my post? – Giannis Paraskevopoulos Jul 28 '13 at 12:52
you can keep it.. No prob – Smaug Jul 28 '13 at 12:55
@jyparask In this situation I would normally upvote the first correct answer and delete mine except if I could think of some additional relevant information to add to it. But entirely up to you. – Martin Smith Jul 28 '13 at 12:59

score 3 · Answer 2 · answered Jul 28 '13 at 12:51

3

Use ADD CONSTRAINT

ALTER TABLE MBR_MST2 ADD CONSTRAINT DF_NewSTS DEFAULT 1 FOR MBR_STS;

answered Jul 28 '13 at 12:51

score 1 · Answer 3 · edited May 23 '17 at 12:13

1

Read the documentation for your statement. Your error is correct, the syntax is incorrect.

Look at this question for the correct syntax.

ALTER TABLE MBR_MST2 ADD CONSTRAINT Your_Constraint_Name DEFAULT 1 FOR MBR_STS

edited May 23 '17 at 12:13

Community

answered Jul 28 '13 at 12:51

BLaZuRE

score 0 · Answer 4 · edited Oct 17 '17 at 07:23

0

ALTER TABLE MBR_MST2 ALTER COLUMN MBR_STS SET DEFAULT 1

Rewrite as

ALTER TABLE MBR_MST2 
add  constraint  DF_MBR_MST2_MBR_STS
Default 'DEFAULT 1' For MBR_STS

Open your table and refresh it's constraints folder and this constraint should be there.

edited Oct 17 '17 at 07:23

Vojtech Ruzicka

answered Oct 17 '17 at 07:06

Raphael

4 Answers4