Alter command showing syntax error in MySQL 8.0

Question

Below is my Table schema

create table employee(
emp_id numeric(4),
fname varchar(10),
lname varchar(10),
mgr_id numeric(4),
hire_date date,
job_id numeric(2),
dept_id numeric(3)
);

Now I want to use ALTER Table command with Alter Column instead of Modify from here

Below is my command :-

ALTER TABLE employee ALTER COLUMN dept_id int(8);

But I am getting a error stating syntax-error :-

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(8)' at line 1

Now if I use Modify it works then why ALTER COLUMN is not working.The command is correct as per the even the official document. I am using MySQL 8.0 .

Answer 1

It seems that you are misunderstanding the manual.

From the link you mentioned the syntax alter column is used for :

ALTER [COLUMN] col_name {
        SET DEFAULT {literal | (expr)}
      | SET {VISIBLE | INVISIBLE}
      | DROP DEFAULT
    }

The following command would work properly.

ALTER TABLE employee ALTER COLUMN dept_id set default 111;

As per the data type changes you have to use MODIFY COLUMN or CHANGE COLUMN

ALTER TABLE employee MODIFY COLUMN dept_id  int(8);
ALTER TABLE employee CHANGE dept_id dept_id  int(8);

ALTER TABLE Statement

Example