CODEWITHSUNDEEP
javamysql
ihavprobs
ihavprobs

Reputation: 1492

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 something like an if condition saying if (expirytime == null) then update endtime to current timestamp?

Upvotes: 1

Views: 616

Answers (3)

Swaranga Sarma
Swaranga Sarma

Reputation: 13423

How about using a insert trigger to do that?

Upvotes: 0

Bohemian
Bohemian

Reputation: 425198

Just select what you want:

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

Upvotes: 3

Nicola Cossu
Nicola Cossu

Reputation: 56397

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

Upvotes: 1

Related Questions