Welcome Guest, Not a member yet? Register   Sign In
DUPLICATE KEY UPDATE in the query builder
#1

I would like to execute the following SQL in the query builder.
Code:
INSERT INTO table1 (`id`, `total`) VALUES (123, 1) ON DUPLICATE KEY UPDATE total = total + 1;

We want to execute 1 if the id does not exist and the existing value + 1 if it exists.

I tried the code below, but it is not what I expected.
PHP Code:
$data = [
    'id' => 100,
    'total' => "todal + 1",
];

$builder $db->table('table1');
$builder->upsert($data); 

Generated queries
Code:
INSERT INTO `table1` (`id`, `total`) VALUES (100,'todal + 1') ON DUPLICATE KEY UPDATE `table1`.`id` = VALUES(`id`), `table1`.`total` = VALUES(`total`)


I was hoping for "upsert" to be available after version 4.3, but is that impossible?
https://www.codeigniter.com/user_guide/d...rting-data

Ultimately, I would like to use "upsertBatch".
Reply


Messages In This Thread
DUPLICATE KEY UPDATE in the query builder - by chanyui - 09-09-2023, 05:20 AM



Theme © iAndrew 2016 - Forum software by © MyBB