SQL merge/upsert

gistfile1.sql

-- CREATE TABLE kohana_test_table (id integer PRIMARY KEY, value integer)

-- MS SQL Server, PostgreSQL, SQLite: conditional insert
INSERT INTO kohana_test_table (id, value)
SELECT 10, 100
WHERE NOT EXISTS
  (SELECT 1 FROM kohana_test_table WHERE id = 10)

-- MySQL: conditional insert
INSERT INTO kohana_test_table (id, value)
SELECT 10, 100
FROM DUAL
WHERE NOT EXISTS
  (SELECT 1 FROM kohana_test_table WHERE id = 10)

-- MySQL, PostgreSQL, SQLite: compatible conditional insert ** does not work on an empty table **
INSERT INTO kohana_test_table (id, value)
SELECT 10, 100
FROM kohana_test_table
WHERE NOT EXISTS
  (SELECT 1 FROM kohana_test_table WHERE id = 10)
LIMIT 1

-- MS SQL Server: compatible conditional insert ** does not work on an empty table **
INSERT INTO kohana_test_table (id, value)
SELECT TOP (1) 10, 100
FROM kohana_test_table
WHERE NOT EXISTS
  (SELECT 1 FROM kohana_test_table WHERE id = 10)

-- MySQL: upsert
INSERT INTO kohana_test_table (id, value) VALUES (10, 100)
  ON DUPLICATE KEY UPDATE value = 100

gistfile2.php

<?php

// Native builders of Real-Database

$db = Database::instance();

$key_conditions = SQL::conditions()
    ->and_column('group_name', '=', $group)
    ->and_column('config_key', '=', $key);

$update = $db->update($table)
    ->value('config_value', serialize($value))
    ->where($key_conditions);

if (0 === $db->execute($update))
{
    $insert = $db->insert($table)
        ->columns(array('config_group', 'config_key', 'config_value'))
        ->values(
            $db->select()
                ->values(array($group, $key, serialize($value)))
                ->where(
                    SQL::conditions()->and_not_exists(
                        $db->select()->value(1)->where($key_conditions)
                    )
                )
        );

    $db->execute($insert);
}