CODEWITHSUNDEEP
sql-server
Nisar
Nisar

Reputation: 6038

Incorrect syntax near the keyword 'SET' IN ALTER STATEMENT

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'.

Upvotes: 3

Views: 5751

Answers (4)

Raphael
Raphael

Reputation: 31

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.

Upvotes: 0

Smaug
Smaug

Reputation: 2673

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

Handy Script Link for Default Constraint

Upvotes: 3

BLaZuRE
BLaZuRE

Reputation: 2406

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

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

Use ADD CONSTRAINT

ALTER TABLE MBR_MST2 ADD CONSTRAINT DF_NewSTS DEFAULT 1 FOR MBR_STS;

Upvotes: 3

Related Questions