PHP
Perform Efficient Batch Insert or Update (Upsert) Operations
Use Laravel Eloquent's `upsert` method to efficiently insert new records or update existing ones in a single database query, optimizing bulk data operations.
use App\Models\Product;
use Illuminate\Support\Facades\DB;
// Assuming 'sku' is unique and 'name', 'price' are columns to update
$productsToUpsert = [
['sku' => 'P001', 'name' => 'Laptop Pro', 'price' => 1200.00, 'stock' => 50, 'created_at' => now(), 'updated_at' => now()],
['sku' => 'P002', 'name' => 'Mouse Wireless', 'price' => 25.50, 'stock' => 200, 'created_at' => now(), 'updated_at' => now()],
['sku' => 'P003', 'name' => 'Keyboard Mechanical', 'price' => 75.00, 'stock' => 100, 'created_at' => now(), 'updated_at' => now()],
// Simulate an existing product being updated
['sku' => 'P001', 'name' => 'Laptop Pro Max', 'price' => 1300.00, 'stock' => 45, 'created_at' => now(), 'updated_at' => now()],
];
// The 'sku' column will be used to determine if a record exists.
// If it exists, 'name', 'price', and 'stock' will be updated.
// The 'created_at' and 'updated_at' timestamps are automatically handled by default.
Product::upsert(
$productsToUpsert,
['sku'], // Unique by 'sku'
['name', 'price', 'stock'] // Columns to update if record exists
);
// Verify the changes
// $product1 = Product::where('sku', 'P001')->first();
// echo "P001 after upsert: " . $product1->name . " " . $product1->price . "
";
How it works: This snippet demonstrates Laravel Eloquent's `upsert` method for efficient batch insert-or-update operations. It takes an array of records, an array of columns that uniquely identify records (e.g., `sku`), and an array of columns to update if a matching record is found. This performs a single, optimized database query, significantly improving performance compared to iterating and performing individual `updateOrCreate` calls. It's ideal for synchronizing large datasets where you need to both insert new items and modify existing ones.