PHP
Bulk Upsert (Insert or Update) Multiple Records with Eloquent
Efficiently synchronize large datasets by performing bulk "insert or update" operations using Laravel Eloquent's `upsert` method in a single database query.
use App\Models\Product;
$productsData = [
['name' => 'Laptop', 'sku' => 'LAP-001', 'price' => 1200.00, 'stock' => 50],
['name' => 'Mouse', 'sku' => 'MOU-002', 'price' => 25.00, 'stock' => 200],
['name' => 'Keyboard', 'sku' => 'KEY-003', 'price' => 75.00, 'stock' => 150],
['name' => 'Laptop', 'sku' => 'LAP-001', 'price' => 1150.00, 'stock' => 60], // Update existing 'Laptop'
['name' => 'Webcam', 'sku' => 'WEB-004', 'price' => 49.99, 'stock' => 100], // New record
];
// Upsert records: Match by 'sku', update 'name', 'price', 'stock' if found
Product::upsert(
$productsData,
['sku'], // Columns to use for unique identification (e.g., primary key or unique index)
['name', 'price', 'stock'] // Columns to update if a matching record is found
);
// After running, 'Laptop' will have its price updated to 1150.00 and stock to 60.
// 'Webcam' will be inserted as a new product.
How it works: The `upsert` method in Laravel Eloquent provides a highly efficient way to insert new records or update existing ones in a single database query. It takes an array of data, an array of unique identifier columns (e.g., `sku`), and an array of columns to update if a matching record is found. This is particularly useful for synchronizing large datasets and improving performance for batch operations.