We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL: how to change this value

davidw88
davidw88 asked
on
Medium Priority
258 Views
Last Modified: 2012-05-07
Hi experts,

I have a table as follows.

mysql> desc message_alert  ;
+---------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id                | int(11)     | NO   | PRI | NULL    | auto_increment |
| dataset_id  | int(11)     | YES  | MUL | NULL    |                |
| dimension1 | varchar(20) | NO   |     | NULL    |                |


Now I want to change the value of column "Null" for "dataset_id" to "NO".  any ideas how to write this SQL?

Thanks so much.
Comment
Watch Question

Commented:
ALTER TABLE message_alert
ALTER COLUMN dataset_id int(11) NOT NULL
 

Commented:
Oh, I answered based on the MySQL.... since your desc statement says mysql
P.
Top Expert 2009

Commented:
Are you asking how to change the default value for the column? If so, you cannot set it to 'NO' because the column is an integer type.

In any case, it is unclear which database you are referring to, your sample shows MySQL but you posted in Oracle and SQL Server zones.

Author

Commented:
This is MySQL.

Hi pssandhu: thanks so much for your prompt reply. I got

mysql> ALTER TABLE message_alert ALTER COLUMN dataset_id int(11) 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 'int(11) NOT NULL' at line 1


any more ideas?

Commented:
What version of MySQL are you running. I am not too familiar wiht all different versions, but there might be a limitation based on your MySQL product version.
P.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
It is Server version: 5.0.67-0ubuntu6 (Ubuntu)

Author

Commented:
Cool! mrjoltcola's sql works!

Thanks experts!

Author

Commented:
Thanks Advisor mrjoltcola for your advice. I did try to post in "MySQL" Zone, however I did not find such a zone so I posted in other related zones.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.