CODEWITHSUNDEEP
mysqlinsert-updatealter
ehime
ehime

Reputation: 8405

Mysql Alter glitch

Hey guys I was trying to alter my tables column to take the current time stamp on creation, the error I'm getting is #1064 - 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 '( 'inspection_number' NOT NULL default CURRENT_TIMESTAMP )' at line 1

I was trying to use

ALTER TABLE `reports`  (
  `inspection_number` DATE NOT NULL default CURRENT_DATE
);

But I"m not seeing the error?

Upvotes: 0

Views: 86

Answers (4)

user319198
user319198

Reputation:

Seems syntax is not correct. Use below :

if adding new column inspection_number:

ALTER TABLE `reports`  ADD COLUMN `inspection_number` timestamp NOT NULL default CURRENT_TIMESTAMP

if modifying existing inspection_number column:

ALTER TABLE `reports`  MODIFY COLUMN  `inspection_number` timestamp NOT NULL default CURRENT_TIMESTAMP

Please specify datatype of column

Upvotes: 1

Rob Taylor
Rob Taylor

Reputation: 604

The syntax you have there is for when you create a table. When you modify a table and want to set a default value use:

ALTER TABLE table_name MODIFY col_name col_type NOT NULL DEFAULT CURRENT_TIMESTAMP;

Upvotes: 0

Aditya Naidu
Aditya Naidu

Reputation: 1380

ALTER TABLE `reports` 
MODIFY `inspection_number` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP ;

Upvotes: 0

Mchl
Mchl

Reputation: 62395

You're missing a MODIFY

ALTER TABLE `reports`  (
  MODIFY `inspection_number` NOT NULL default CURRENT_TIMESTAMP
);

Upvotes: 0

Related Questions