Antwoord

SQL biedt verschillende manieren om gegevens in een tabel bij te werken of in te voegen:

UPDATE — wijzigt bestaande records die aan de voorwaarde voldoen.
MERGE (of UPSERT) — combineert de logica van invoegen en bijwerken in één opdracht: als de rij is gevonden — wordt deze bijgewerkt; als dat niet zo is — wordt deze toegevoegd.
INSERT ... ON DUPLICATE KEY UPDATE (MySQL) of INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) — voegt een nieuwe rij toe en werkt een bestaande bij in geval van een sleutelconflict.

Wanneer en wat te gebruiken:

Als je zeker weet dat de records nog niet bestaan — gebruik INSERT.
Voor het bijwerken van bestaande records — UPDATE met voorwaarden.
Als je niet weet of een record bestaat, is het efficiënter om MERGE of UPSERT te gebruiken — dit bespaart complexiteit en vermindert het aantal SQL-opvragen.
UPSERT-operaties zijn meestal sneller bij een groot aantal records en minimaliseren race conditions.

Voorbeeld (PostgreSQL):

INSERT INTO employees(id, name, salary)
VALUES (1, 'Ivan', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

Invloed op prestaties en integriteit:

Meerdere afzonderlijke UPDATE/INSERT kunnen leiden tot race conditions en blokkades.
MERGE/UPSERT is efficiënter bij massale migratie en ondersteunt atomiciteit.

Vraag met een gokje

Wat gebeurt er als je probeert een UPSERT uit te voeren op een uniek veld, maar tegelijkertijd in dezelfde transactie dezelfde rij probeert in te voegen en bij te werken?

Correcte antwoord: Er kan een vergrendelings-/conflictfout optreden, omdat de transactie probeert hetzelfde record twee keer te wijzigen. De operatie wordt niet voltooid of eindigt met een deadlock-fout, afhankelijk van de DBMS.

Voorbeeld:

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'Oleg') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
UPDATE users SET name = 'Petr' WHERE id = 1;
COMMIT;

Bij een verkeerde volgorde en isolatieniveau kan er een wijzigingsconflict ontstaan.

Verhaal №1

In een gegevensmigratielogica gebruikten ze een combinatie van SELECT, gevolgd door INSERT of UPDATE, wat er soms toe leidde dat een andere transactie de gegevens eerder instelde, resulterend in een fout "duplicate key". Dit veroorzaakte frequente mislukkingen van de nachtelijke gegevensexport, waardoor de logica moest worden vervangen door UPSERT.

Verhaal №2

In een MySQL-project werd ON DUPLICATE KEY UPDATE onjuist toegepast, wat de samengestelde unieke sleutels verkeerd verwerkte. Dit resulteerde in het niet bijwerken van een deel van de gegevens, terwijl duplicaten verschenen. Het probleem kon alleen op de productieomgeving worden vastgesteld.

Verhaal №3

In een project werd UPDATE in plaats van UPSERT gebruikt voor de synchronisatie van gebruikersgegevens. Bij verbindingsfouten ging een deel van de gegevens verloren omdat de update werd overgeslagen voor nieuwe gebruikers. Na analyse werd MERGE/UPSERT volledig geïmplementeerd.

Leg de verschillen uit tussen de commando's UPDATE, MERGE en INSERT ... ON DUPLICATE KEY UPDATE in SQL. In welke gevallen is welke aanpak te verkiezen, en hoe beïnvloeden deze hulpmiddelen de prestaties en integriteit van de gegevens?

Antwoord

Vraag met een gokje