Mark, With the CHANGE clause of ALTER TABLE statement, you must provide the column definition, so something like this is what you need: ALTER TABLE actors CHANGE director_id actor_id MEDIUMINT UNSIGNED NOT NULL; or whatever your original definition is.
AFAIK there is no way to rename a column without giving the column type. --Rob > Hi All, gee I really hate bugging you all for this. I looked at this page, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html which has this, To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| as well as renaming it from |b| to |c|: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql> ALTER TABLE actors CHANGE director_id actor_id; I get this, 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 '' at line 1 Sorry, little confused right about now, eh. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ < ---------- Original Message ---------- FROM: Mark Sargent <[EMAIL PROTECTED]> TO: mysql@lists.mysql.com DATE: Thu, 08 Jun 2006 00:09:45 +1000 SUBJECT: ERROR 1064 (42000): You have an error in your SQL syntax; Hi All, gee I really hate bugging you all for this. I looked at this page, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html which has this, To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| as well as renaming it from |b| to |c|: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql> ALTER TABLE actors CHANGE director_id actor_id; I get this, 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 '' at line 1 Sorry, little confused right about now, eh. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]