Can I combine these two update statements?

Spudhead · #1 (**permalink**) 05-21-09, 12:41

I have a suspicion that the answer is "no", but I never found out nuffink by not asking, so:

Code:

UPDATE Greetings SET openDate = now() WHERE idGreetings = 123 AND openDate IS NULL

UPDATE Greetings SET greetingOpenCount = (greetingOpenCount+1) WHERE idGreetings = 123

My application logic has forced me into a position where I'm running those two straight after each other: I want the "open" count incremented each time a greeting is opened, but I only want to insert the date if it's not already set.

Is there any way to turn my two DB calls into one?

Thanks

r937 · #2 (**permalink**) 05-21-09, 14:56

Quote:

Originally Posted by Spudhead

I have a suspicion that the answer is "no"

your suspicionating was incorrect

Code:

UPDATE Greetings 
   SET greetingOpenCount = greetingOpenCount + 1 
     , openDate = CASE WHEN openDate IS NULL
                       THEN CURRENT_TIMESTAMP
                       END
 WHERE idGreetings = 123

Internet Services

Web Development

Digital Marketing

Consumer Tech