changing default value to a sub-query in mysql

Question

What I want to do, is that I'd like to change the column properties, and associate a default value for a certain column, and this value would be from a sub-query. Basically the default value of timeOfCreation should be a subquery--> (select NOW()).But for some reason it returns a syntax error, which I don't understand. Here's my command, which I input.

 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';



ALTER TABLE `horizon`.`articles` CHANGE COLUMN `timeOfCreation` `timeOfCreation` DATETIME NULL DEFAULT (select NOW())  ;



 SQL_MODE=@OLD_SQL_MODE;

    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

And the error:

Executing SQL script in server

ERROR: Error 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 '(select NOW())' at line 1





ALTER TABLE `horizon`.`articles` CHANGE COLUMN `timeOfCreation` `timeOfCreation` DATETIME NULL DEFAULT (select NOW())  



SQL script execution finished: statements: 3 succeeded, 1 failed

Ps. I'm using workbench as a software, but I don't think it would matter.

Barmar · Accepted Answer · 2013-04-30 08:42:24Z

3

The default value has to be a constant, it can't be an expression or a query. There's one exception, which happens to be the case you need: a TIMESTAMP or DATETIME column can use CURRENT_TIMESTAMP as its default value.

ALTER TABLE `horizon`.`articles` MODIFY COLUMN `timeOfCreation` DATETIME NULL DEFAULT CURRENT_TIMESTAMP  ;

answered Apr 30, 2013 at 8:42

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ThomasMX Over a year ago

Brilliant! Now it works. What I did, was that I used CURRENT_TIMESTAMP as default value AND I also changed the column type to timestamp. for some reason if I leave it as datetime, it will say "invailid value" error. Thanks.

Barmar Over a year ago

MySQL 5.6 extended it to DATETIME, prior to that it only workse for TIMESTAMP.

Barmar Over a year ago

if this is the solution, why haven't you accepted the answer?

ThomasMX Over a year ago

Sorry, my fault :) I'm quite new to this site, and I shall learn things. I thought I have to vote up the correct answer, but I am not allowed to do it. Now I've accepted your answer

Collectives™ on Stack Overflow

changing default value to a sub-query in mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related