CODEWITHSUNDEEP
sqlsql-serveroracle-database
zola25
zola25

Reputation: 1921

Update field with default constraint value

If a column has a default constraint on it, how do I update existing records to set it to that default value?

e.g. I want to do something like

UPDATE table1
SET col1 = DEFAULT(col1)
WHERE col2 = xxx

Upvotes: 1

Views: 1099

Answers (2)

Sahi
Sahi

Reputation: 1484

you can try this.

 Create table tab1(id int, def int constraint df_def Default 10)

INSERT INTO tab1(id) VALUES(1)
INSERT INTO tab1(id,def) VALUES(2,15)

DECLARE @DefVal INT

SELECT @DefVal=REPLACE(REPLACE(object_definition(default_object_id),'(',''),')','')
FROM   sys.columns
WHERE  name      ='def'
AND    object_id = object_id('dbo.tab1')

UPDATE tab1 SET def=@DefVal WHERE id=2

Upvotes: 0

Manish Kumar
Manish Kumar

Reputation: 184

Set as following:

UPDATE table1
 SET col1 = DEFAULT
 WHERE col2 = xxx

Upvotes: 4

Related Questions