CODEWITHSUNDEEP
sql-server-2008
kenshin
kenshin

Reputation: 177

SQL update to allow null values

I have table in MSSQL that has a column where the value can not be null. But there are update operations where i want to set that column to null when i update the other columns. Is there such a way to achieve this , or should i recreate the table , allowing the column to take null values.

Upvotes: 16

Views: 35459

Answers (4)

scibuff
scibuff

Reputation: 13755

You could probably do something like this:

    "ALTER TABLE table ADD COLUMN newcolumn VARCHAR(255)"
    "UPDATE TABLE table SET newcolumn = oldcolumn"
    "ALTER TABLE table DROP COLUMN oldcolumn"
    "ALTER TABLE table ADD COLUMN oldcolumn VARCHAR(255)"
    "UPDATE TABLE table SET oldcolumn = newcolumn"

Upvotes: 1

t_motooka
t_motooka

Reputation: 565

use ALTER TABLE statement to allow null values.

see http://msdn.microsoft.com/en-us/library/ms190273.aspx

Upvotes: -1

Justin Pihony
Justin Pihony

Reputation: 67075

You will need to update the schema. There is no other way. That is the reason for constraints. You can perform an alter to avoid totally recreating the table if you have data that would not be lost, that is

Upvotes: 0

oryol
oryol

Reputation: 5248

You should allow null for your column: alter table T1 alter column C1 int null

Upvotes: 45

Related Questions