Can you do selective Inserting or Updating?

Question

I am trying to create a timesheet and want to have the following SQL Query done:

I have the fields datebeginning and dateending and jobid and I want to only add a new one if no open one exists for a given job id and if not I want the query to update the fild dateending for the given id. Is there a way to do that without using PHP to first Select and then Insert or Update?

Answer 1

Use INSERT ... ON DUPLICATE KEY UPDATE ..., see http://dev.mysql.com/doc/refman/5.0/en/insert.html

Answer 2

INSERT INTO table (jobid, datebeginning, dateending) VALUES (1,'2009/03/03','2009/05/05')
ON DUPLICATE KEY UPDATE dateending='2009/06/06';

Answer 3

Since you have the question tagged as MySQL, you might also consider using 'REPLACE INTO', this of course depends on you filling in all fields, as this command inserts or deletes and inserts, as opposed to updating just the one field. If providing all fields, REPLACE INTO would work just as well.

http://dev.mysql.com/doc/refman/5.0/en/replace.html