"Upsert" Trick: Syncing Data Without the "If Exists" Loop

When syncing data from an external API (like updating product prices or user details), developers often write logic like this:

1. Check if the record exists (SELECT).

2. If yes, UPDATE.

3. If no, INSERT.

This requires two database round-trips. In a loop of 1,000 items, that is 2,000 queries.

MySQL has a native solution: INSERT ... ON DUPLICATE KEY UPDATE.

The Old Way (PHP Logic):

                    $user = User::find($id);
if ($user) {
    $user->update(['email' => $email]);
} else {
    User::create(['id' => $id, 'email' => $email]);
}
                

The Modern Way (SQL / Laravel Upsert):

                    // MySQL handles the logic internally. 1 Query total.
DB::table('users')->upsert(
    [
        ['id' => 1, 'email' => 'new@atomic.gr'],
        ['id' => 2, 'email' => 'hello@atomic.gr']
    ],
    ['id'], // The unique column to check
    ['email'] // The columns to update if it exists
);
                

Why use it? It is atomic and significantly faster for bulk operations. You can sync thousands of records in a single query.