0

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 enter image description 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 .

1 Answer 1

6

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

1
  • Thank you very much for clearing my doubt and explaining it in such an elaborate manner. Commented Apr 4, 2023 at 4:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.