Insert if not exists in DB and delete if not in an array

Question

I have an array like this :

$services = array(
               array("id" => "1", "desc" => "desc 1"), 
               array("id" => "2", "desc" => "desc 2" ),
               ......
            );

I want to insert those services in TABLE_SERVICE . each service is inserted if it doesnt exists in TABLE_SERVICE , and deleted it if it exists in TABLE_SERVICE but not in $services array.

I could just delete all records in TABLE_SERVICE and then insert all $services elements , but that can be an issue with performance because i often have large set of data in both TABLE_SERVICE and $services .

So is there an efficient way to do this ?

Thanks.

score 2 · Accepted Answer · edited May 23 '17 at 12:27

2

If it was me I'd iterate over $services collecting ids:

$ids = array();
foreach($services as $service)
{
    $ids[] = $service['id'];
}

Then using PHP's join and the select NOT IN

"DELETE FROM TABLE_SERVICE WHERE id NOT IN (" . join($ids,',') . ")"

After that, iterate over the array again to insert/update using ON DUPLICATE KEY

"INSERT INTO TABLE_SERVICE (id,desc) VALUES (?,?) ON DUPLICATE KEY UPDATE desc = ?"

Since oracle doesn't have ON DUPLICATE KEY this stackoverflow question might help you with that last part.

edited May 23 '17 at 12:27

Community

1
1

answered Feb 17 '12 at 09:29

Kristoffer Sall-Storgaard

10,576
5
36
46

unfortunately Oracle doesn't support ON DUPLICATE KEY. That's a MySql Syntax – winkbrace Feb 17 '12 at 11:01

score 0 · Answer 2 · answered Feb 17 '12 at 11:21

My answer would be that there isn't really an efficient way to do this. I have thought about a merge, but to be efficient, you would still be better off first inserting it in a temporary table. Then you might as well just truncate table_service and then fill it again from your $service array. Even if Kristoffer's anwser could work, it might still be slower than a truncate insert.

This is my php method to quickly insert a lot of records: The advantage of this is, that your insert statement will be parsed only once instead for each insert, which will improve the speed greatly. Sometimes by a factor 100 or so.

$connection = oci_connect(<YOUR CONNECTION>);
$sql = insert into table_service (id, var) values (:id, :var); // desc is a reserved word, cannot be a column name
$parsed = oci_parse($connection, $sql);
$binds = array(':id', ':var');
$sizes = array(6, 20);
$data = $services;
$errors = execute_multiple($binds, $sizes, $data);
if ($errors > 0)
    // log or show
else
    // feedback: full succes!

function execute_multiple($binds, $sizes, $data, $commit = true)
{
    $errorCount = 0;

    // first determine all binds once
    foreach ($binds as $i => $bind)
    {
        // ${trim($bind, ':')} example:  :some_id -> $some_id
        oci_bind_by_name($parsed, $bind, ${trim($bind, ':')}, $sizes[$i]);
    }

    // Then loop over all rows and give the variables the new value for that row
    // This is because the variables remain binded!
    for ($row=0; $row<count($data); $row++)
    {
        foreach ($binds as $i => $bind)
        {
            $value = array_key_exists($i, $data[$row]) ? substr($data[$row][$i], 0, $sizes[$i]) : null;
            ${trim($bind, ':')} = trim($value);
        }

        if (! @oci_execute($this->parsed, OCI_DEFAULT))  // don't auto commit
            $errorCount++;
    }

    if ($commit)
        oci_commit($connection);

    return $errorCount;
}

Insert if not exists in DB and delete if not in an array

2 Answers2