I'm using mysql

Question

I got an issue that i want to drop the default date that the AddedDate_in_Ward generate, i tried to remove it using (Alter), but it says that i have an incorrect syntax near of my columns name. any help please

CREATE TABLE In_patient(
inPatient_id INT primary key not null,
Patient_id INT REFERENCES Patient(Patient_id),
Ward_required Varchar(15),
AddedDate_to_waitng_list Date,
AddedDate_in_Ward Date,
Expected_leave_date Date,
Actual_leave_date Date,
);

ALTER TABLE In_patient
ALTER AddedDate_in_Ward DROP DEFAULT;

Welcome to StackOverflow! Note that the `CREATE TABLE` statement shown in the question will throw a syntax error, due to the trailing comma right before the close paren. Use `SHOW CREATE TABLE` statement to output the current table definition. The syntax to change a column definition would be `ALTER TABLE In_patient CHANGE AddedDate_in_Ward AddedDate_in_Ward DATE`, But its not clear what we are actually attempting to accomplish. — spencer7593, May 18 '18 at 04:59

score 0 · Answer 1 · answered May 18 '18 at 04:37

0

I belive the additional comma is the issue here

Actual_leave_date Date,);

Correct Query would be

CREATE TABLE In_patient(inPatient_id INT primary key not null, Patient_id INT REFERENCES Patient(Patient_id), Ward_required Varchar(15), AddedDate_to_waitng_list Date, AddedDate_in_Ward Date, Expected_leave_date Date, Actual_leave_date Date);

If you're trying to remove a column with alter,

ALTER TABLE In_patient DROP COLUMN AddedDate_in_Ward;

answered May 18 '18 at 04:37

oturan

329
3
19

yeah, i got what u mean, but I actually dont want to remove the column, i just need to drop the default value of that column – Mousa Alsarah May 19 '18 at 07:52
`ALTER TABLE In_patient ALTER COLUMN AddedDate_in_Ward DROP DEFAULT;` You may find more help [here](https://stackoverflow.com/questions/41810796/mysql-remove-default-value-for-datetime-field). To remove the already inserted default values in the table you can use `UPDATE` query. – oturan May 21 '18 at 14:49

score 0 · Answer 2 · answered May 18 '18 at 04:45

If you want to set current_time stamp for that column

ALTER TABLE table_name CHANGE column_name DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE In_patient ALTER COLUMN AddedDate_in_Ward DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE In_patient modify COLUMN AddedDate_in_Ward DEFAULT CURRENT_TIMESTAMP;

I'm using mysql

2 Answers2