开发者

While executing mysql insert select query- can we do condition check?

I am trying to execute mysql select insert query as below.

INSERT INTO ACCOUNTS.TABLE1 (NAME,ID,STARTTIME,ENDTIME) SELECT NAME,ID,CREATIONTIME,EXPIRYTIME FROM ACCOUNTS.TABLE2 WHERE ...;

Now, if EXPIRYTIME in table2 is null, then i want to update the ENDTIME in table1 to current timestamp. I have overriden the default behavior of this timestamp field by the description

ENDTIME TIMESTAMP NULL DEFAULT NULL

for some reason.

Is it possible in the above select insert query to include some some开发者_高级运维thing like an if condition saying if (expirytime == null) then update endtime to current timestamp?


Just select what you want:

INSERT INTO ACCOUNTS.TABLE1 (NAME,ID,STARTTIME,ENDTIME) 
SELECT NAME,ID,CREATIONTIME, ifnull(EXPIRYTIME, now())
FROM ACCOUNTS.TABLE2 WHERE ...;


INSERT INTO ACCOUNTS.TABLE1 (NAME,ID,STARTTIME,ENDTIME) 
SELECT NAME,ID,CREATIONTIME,if(EXPIRYTIME is null,now(),expirytime) 
FROM ACCOUNTS.TABLE2 WHERE ...;


How about using a insert trigger to do that?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜