Writes and Upserts

Writes and Upserts

Inserts, Updates, Upserts, and Deletes

insert() takes a row or a list of rows; insertGetId() returns the new key (Inserts and IDs). update() and delete() return affected-row counts, and a forgotten where() hits the whole table. increment() and decrement() compute on the server, safe where PHP arithmetic is not.

Write methods against the shop, with the SQL each one sentPHP

DB::listen(fn ($e) => print(wordwrap($e->toRawSql(), 95)."\n"));
$n = DB::table('customers')->insertOrIgnore([
    ['email' => 'ana@example.com', 'name' => 'Ana again', 'country' => 'BR'],
    ['email' => 'jo@example.com', 'name' => 'Jo Park', 'country' => 'KR']]);
$m = DB::table('products')->upsert([
  ['sku' => 'AC-MUG-01', 'title' => 'Mug', 'category_id' => 5, 'price' => 13.5, 'stock' => 55],
  ['sku' => 'AC-PEN-01', 'title' => 'Pen', 'category_id' => 5, 'price' => 3.25, 'stock' => 9],
], uniqueBy: ['sku'], update: ['price', 'stock']);
echo "-> $n, $m\n";

Output

insert ignore into `customers` (`country`, `email`, `name`) values ('BR', 'ana@example.com',
'Ana again'), ('KR', 'jo@example.com', 'Jo Park')
insert into `products` (`category_id`, `price`, `sku`, `stock`, `title`) values (5, 13.5,
'AC-MUG-01', 55, 'Mug'), (5, 3.25, 'AC-PEN-01', 9, 'Pen') on duplicate key update `price` =
values(`price`), `stock` = values(`stock`)
-> 1, 3

INSERT IGNORE skipped Ana's duplicate email, but it downgrades other errors to warnings too. upsert() is Upserts's ON DUPLICATE KEY UPDATE; its 3 is MySQL 524 's count, 2 for the updated mug and 1 for the new pen, and MySQL ignores uniqueBy, matching every unique key. updateOrInsert() reads, then writes, leaving a race window; prefer upsert().

MENU