DUPLICATE KEY UPDATE in the query builder

DUPLICATE KEY UPDATE in the query builder - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: DUPLICATE KEY UPDATE in the query builder (/showthread.php?tid=88444)

DUPLICATE KEY UPDATE in the query builder - chanyui - 09-09-2023

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/database/query_builder.html#upserting-data

Ultimately, I would like to use "upsertBatch".

RE: DUPLICATE KEY UPDATE in the query builder - InsiteFX - 09-10-2023

PHP Code:
$data = [
    'id' => 100,
    'total' => "todal + 1", // <-- this will not add 1 to total!
];

// but this will.

$total = $total +1;

$data = [
    'id' => 100,
    'total' => $total,
]; 

RE: DUPLICATE KEY UPDATE in the query builder - chanyui - 09-11-2023

Thank you for your answer.
I don't think you understood the intent of my question, so I will change my explanation.

I want to generate the following MySQL query using the query builder.

Code:
INSERT INTO table1 (`id`, `total`) VALUES (123, 1) ON DUPLICATE KEY UPDATE total = total + 1;

reference
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Can this be accomplished with Codeigniter's Query Builder?
https://www.codeigniter.com/user_guide/database/query_builder.html?highlight=query#upsert

Currently, we have no choice but to use the following method to achieve this situation.

PHP Code:
$data = [
    'id' => 100,
    'total' => 1,
];

$sql = "INSERT INTO table1 (id, total) VALUES (?, ?) ON DUPLICATE KEY UPDATE total = total + ?";
$db->query($sql, [$data['id'], $data['total'],  $data['total']]); 

RE: DUPLICATE KEY UPDATE in the query builder - ozornick - 09-11-2023

This is probably the best solution