Jamie Hartnoll Jamie Hartnoll - 2 months ago 10
MySQL Question

Insert Values from another Row and handle row does not exist. Mysql insert

I have a question/answer system and am trying to record response time when a question is replied to.

Simple snippet of the

INSERT
code I am using:

INSERT INTO questions (responsetime, {otherfields})
(SELECT Now-msgDateTime, {otherfields} FROM questions WHERE headerid='This_Header_id' ORDER BY id DESC LIMIT 1)


This works absolutely fine for all secondary messages in a thread, but for the first message in a thread it will have a new and unique
HeaderId
, so a query

(SELECT * FROM questions WHERE headerid='This_Header_id')


Will return no values. In this case, my first SQL update will not
INSERT
a new row.

So how do I write a fallback to insert '0' into the
responsetime
field where the row selected by
headerid
is
NULL
?

Answer

You should be able to use a group by function like MAX which will return either null or a matching value. If there's a better way to do it, would love to see other answers but suspect this will work for you.

INSERT INTO questions (responsetime, {otherfields})
 (SELECT IF(ISNULL(MAX(headerid)), 0, Now-msgDateTime)
   , {otherfields} 
  FROM questions WHERE headerid='This_Header_id' 
  ORDER BY id DESC 
  LIMIT 1
)
Comments