error while updating table

Question

Post reply

error while updating table

thatishari

SSC Rookie

Points: 33
More actions
January 3, 2012 at 3:12 am

#391235

Hi all,
I am modifying a table which has 6 columns with 1 column as not null. now what i have to do is have to add another column as not null which is with data type integer.
While using this [highlight=#ffff11]ALTER TABLE dbinformation MODIFY database_owner_id int(3)not null[/highlight]
and doing i am getting the below error
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'modify'
SQL server version is 2005 with SP2.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

ALTER TABLE:

ALTER TABLE <name> ALTER COLUMN <name> <type> ...

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

thatishari SSC Rookie Points: 33 More actions · Answer 2

Hi,

getting this error

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'Database_Owner_ID', table 'DBATEAM.dbo.DBINFORMATION'; column does not allow nulls. UPDATE fails.

The statement has been terminated.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

thatishari (1/3/2012)
Hi all,
I am modifying a table which has 6 columns with 1 column as not null. now what i have to do is have to add another column as not null which is with data type integer.

If you want the column to be NOT NULL, you have to explicitly add a DEFAULT value:

ALTER TABLE someTable ADD newColumnName int NOT NULL DEFAULT 0;

-- Gianluca Sartori

okbangas SSChampion Points: 11773 More actions · Answer 4

The error message tells you exactly what is wrong: There are NULLs in your column, and the new table definition does not allow nulls. Assign a value the column where it is null.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 5

Hi...

Hey actually you trying to add a column into table that have already containe records and when you introduce a new column with not null that means when new column comes into table it require a value for all the rest of the record in table...

Firstly to add a new column into table you need a syntax as

ALTER TABLE CHART ADD NEWCOLUMN INT NOT NULL

but this have given you a error reason is records are all ready present in table and there is no value for this newcolumn

To do the things right you can do two things.

1. First is the way as show above

ALTER TABLE CHART ADD NEWCOLUMN INT NOT NULL DEFAULT 0

this will consider the value 0 for rest of all record that are already present in a table.

2.If you want you desire value rather then giving 0 and if you wish u dont want to keep any default value for this column you need to do some hard work to do this...

a.add new column in table without not null

ALTER TABLE CHART ADD NEWCOLUMN INT this will allow you to add a column without any problem then you can update your desire value you want , then finally with another alter table you can bring this colmn inot not null

ALTER TABLE TABLE1 MODIFY NEWCOLUMN INT NOT NULL

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

thatishari SSC Rookie Points: 33 More actions · Answer 6

Thanks Okbangas and Gianluca.. I have modified the null values in that column and changed it to not null using

ALTER TABLE <name> ALTER COLUMN <name> <type> ... 🙂