Can this query be any more DRY?

Question

I've just checked out the ON DUPLICATE KEY UPDATE for MySQL.

Here is an example query.

$query = 'INSERT INTO `activities`
                  (`id`,
                   `hole_id`,
                   `name_id`,
                   `start_depth`,
                   `end_depth`,
                   `start_time`,
                   `end_time`
                  ) VALUES (
                :id,
                :hole_id,
                :name_id,
                :start_depth,
                :end_depth,
                :start_time,
                :end_time
            ) ON DUPLICATE KEY UPDATE
               `id` = :id,
               `hole_id` = :hole_id,
               `name_id` = :name_id,
               `start_depth` = :start_depth,
               `end_depth` = :end_depth,
               `start_time` = :start_time,
               `end_time` = :end_time
            ';

There is a lot of repetition there obviously.

Is there a way to say "insert, or if exists use the existing information to update".

I've looked at REPLACE, and it says it inserts and deletes if neccessary. The docs say to insert or update to use the method I've used above.

So can I eliminate doubling up of all that update info?

Answer 1

You can use the VALUES() function to refer to the value of a column rather than repeating the value in the ON DUPLICATE KEY portion. See: http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_values.

For example:

$query = 'INSERT INTO `activities`
              (`id`,
               `hole_id`,
               `name_id`,
               `start_depth`,
               `end_depth`,
               `start_time`,
               `end_time`
              ) VALUES (
            :id,
            :hole_id,
            :name_id,
            :start_depth,
            :end_depth,
            :start_time,
            :end_time
        ) ON DUPLICATE KEY UPDATE
           `id` = VALUES(id),
           `hole_id` = VALUES(hole_id),
           `name_id` = VALUES(name_id),
           `start_depth` = VALUES(start_depth),
           `end_depth` = VALUES(end_depth),
           `start_time` = VALUES(start_time),
           `end_time` = VALUES(end_time)
        ';