Alter Table and NOT NULL

[0xCMD]

I have the following table:

mysql> describe LEVEL1_CUSTOMER;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| CUSTOMER_NUM  | char(3)      | NO   | PRI | NULL    |       |
| CUSTOMER_NAME | char(50)     | YES  |     | NULL    |       |
| BALANCE       | decimal(8,2) | YES  |     | NULL    |       |
| CREDIT_LIMIT  | decimal(8,2) | YES  |     | NULL    |       |
| REP_NUM       | char(2)      | YES  |     | NULL    |       |
| CUSTOMER_TYPE | char(1)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

I want to change CREDIT_LIMIT to not accept NULLS, I tried the following command but it gives me an ambiguous error. am I doing the syntax right, I got that command from a mysql book so I assume it should work.

mysql> alter table LEVEL1_CUSTOMER
    -> modify CREDIT_LIMIT not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'not null' at line 2

[1stDAN]

The complete column specification should be applied:

mysql> alter table LEVEL1_CUSTOMER modify CREDIT_LIMIT DECIMAL(8,2) NOT NULL;

You should notice that in standard sql you can't change from NULL to NOT NULL if there are already rows having NULL values. You can count the rows containing NULL values:

select count(*) from LEVEL1_CUSTOMER where CREDIT_LIMIT IS NULL