Can any one tell what is the wrong in this query?

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008R2

Dear Friends,

I have already created a table name as Student, now I have 10 records in that table. Unfortunately I forgot that to set a default value for Result column, now I have tried to set default value for Result column but it shows the error on set. The bellow query I have to used.

SQL Quey: alter table Student alter Result set default 'Pass'

Thanks in advance.

Posted 10-Sep-14 20:35pm

Member 10021658

Add a Solution

Comments

MuhammadUSman1 11-Sep-14 2:46am

Check solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2014-09-10T20:45:00

Solution 2

Try this,

ALTER TABLE STUDENT ADD CONSTRAINT CONS_DF_RESULT DEFAULT N'Pass' FOR Result;

Posted 10-Sep-14 20:45pm

_Asif_

Comments

Member 10021658 11-Sep-14 2:51am

Thanks for reply Syed. The query is execute successfully, but its not updated for already existing records.

_Asif_ 11-Sep-14 2:54am

A simple Update is required to update existing records.

UPDATE STUDENT
SET Result = 'Pass'
WHERE RESULT IS NULL;

Member 10021658 11-Sep-14 2:59am

(0 row(s) affected)

_Asif_ 11-Sep-14 3:02am

What value is set in Result for existing records?

Member 10021658 11-Sep-14 3:05am

datatype is varchar(50) and Allow Nulls is checked

Member 10021658 11-Sep-14 3:26am

But I have using these query update student set result='Pass' where result=''
Its working properly. But before I have tried to insert record using insert query constraint like CONS_DF_RESULT its not inserted default value, but using gui mode its working.

OriginalGriff · Answer 2 · 2014-09-10T20:44:00

Solution 1

Try:

SQL

ALTER TABLE Student
ALTER COLUMN Result SET DEFAULT 'Pass'

Posted 10-Sep-14 20:44pm

OriginalGriff

Comments

Member 10021658 11-Sep-14 2:55am

Thanks for reply Griff. Whenever I have tried this one ALTER TABLE Student ALTER COLUMN Result SET DEFAULT 'Pass', I got the error message like Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SET'.

MuhammadUSman1 · Answer 3 · 2014-09-10T20:45:00

Solution 3

use this

SQL

ALTER TABLE Student 
ALTER COLUMN Result set default 'Pass'
-- you was missing column

Posted 10-Sep-14 20:45pm

MuhammadUSman1

v2

Comments

Member 10021658 11-Sep-14 3:01am

I think it is working on empty table only.

Manikandan MAC 11-Sep-14 7:43am

Its not working, shows error,

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'set'.

I hope that Solution 1 & 3, will not work.

Solution 2 is working good,

Can any one tell what is the wrong in this query?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Can any one tell what is the wrong in this query?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us