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?
精彩评论