[sql] Altering a column to be nullable

I want to alter a table column to be nullable. I have used:

ALTER TABLE Merchant_Pending_Functions Modify NumberOfLocations NULL

This gives an error at Modify. What is the correct syntax?

Oracle

ALTER TABLE Merchant_Pending_Functions MODIFY([column] NOT NULL);

In PostgresQL it is:

ALTER TABLE tableName ALTER COLUMN columnName DROP NOT NULL;

This depends on what SQL Engine you are using, in Sybase your command works fine:

ALTER TABLE Merchant_Pending_Functions 
Modify NumberOfLocations NULL;

As others have observed, the precise syntax for the command varies across different flavours of DBMS. The syntax you use works in Oracle:

SQL> desc MACAddresses
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMPUTER                                           NUMBER
 MACADDRESS                                         VARCHAR2(12)
 CORRECTED_MACADDRESS                      NOT NULL VARCHAR2(17)

SQL> alter table MACAddresses
  2       modify corrected_MACAddress null
  3  /

Table altered.

SQL> desc MACAddresses
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMPUTER                                           NUMBER
 MACADDRESS                                         VARCHAR2(12)
 CORRECTED_MACADDRESS                               VARCHAR2(17)

SQL>

If this was MySQL syntax, the type would have been missing, as some other responses point out. Correct MySQL syntax would have been:

ALTER TABLE Merchant_Pending_Functions MODIFY NumberOfLocations INT NULL

Posting here for clarity to MySQL users.

Although I don't know what RDBMS you are using, you probably need to give the whole column specification, not just say that you now want it to be nullable. For example, if it's currently INT NOT NULL, you should issue ALTER TABLE Merchant_Pending_Functions Modify NumberOfLocations INT.

for Oracle Database 10g users:

alter table mytable modify(mycolumn null);

You get "ORA-01735: invalid ALTER TABLE option" when you try otherwise

ALTER TABLE mytable ALTER COLUMN mycolumn DROP NOT NULL;

ALTER TABLE Merchant_Pending_Functions MODIFY COLUMN `NumberOfLocations` INT null;

This will work for you.

If you want to change a not null column to allow null, no need to include not null clause. Because default columns get not null.

ALTER TABLE Merchant_Pending_Functions MODIFY COLUMN `NumberOfLocations` INT;

For HSQLDB:

ALTER TABLE tableName ALTER COLUMN columnName SET NULL;