sql statement

2007-11-25 1:37 am
我想問下如果我想將個column由not null 轉成null,可以點做呢? 如果個column一路落黎都係有入野!

回答 (3)

2007-11-25 3:00 am
✔ 最佳答案
可以執行以下statement
update [tableName] set [columnName] = null where [columnName] is not null

update [tableName] set [columnName] = null
(因為把null 的column轉做null是不會影響結果, 而且沒有where clause會執行得快些)
當中, [tableName], [columnName] 換上相應的table和column名
2007-11-30 7:08 am
alter table abc
change colA varchar2(20) default null ;

2007-11-29 23:08:43 補充:
alter table abcalter column colA colA varchar2(20) default null ;
參考: self
2007-11-27 4:18 pm
To change the DEFINITION of a table (not the data in the table): Use ALTER TABLE
Depending on how to defined the table, there are different ways that you can do it:
If you have defined constraint names (eg you added the NOT NULL constraint by using:
ALTER TABLE [tablename] ADD CONSTRAINT [constraint name] CHECK ([columnname] NOT NULL);)
then you can simply drop the associated constraint:
ALTER TABLE [tablename] DROP CONSTRAINT [the constraint name that belongs to the NOT NULL column];
If the constraint does not have a name (or you do not know the name), you can try redefining the entire table:
ALTER CREATE TABLE [tablename] (
[columnname1] [columntype1] {any constraints like PRIMARY KEY, etc},
...
)
In this method, you put all columns that was in the db before in the statement, along will all constraints that were defined. The table will be changed to exactly what you have redefined. To drop the NOT NULL constraint, you just don't put the keyword NOT NULL in at the end of the column definition.
The second method does not work in most database implementations, but is specified in the SQL standard.


2007-11-27 08:20:12 補充:
References:http://www.mckoi.com/database/SQLSyntax.html#3http://ugweb.cs.ualberta.ca/~c391/manual/chapt6.html (specific to Oracle)

2007-11-27 08:21:28 補充:
If you leave out any column in the second method, the column will be deleted!! Be careful!!


收錄日期: 2021-04-13 14:34:57
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20071124000051KK03085

檢視 Wayback Machine 備份