El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Introducció

L'instrucció UPSERT (una combinació de "UPDATE" i "INSERT") és una operació que permet inserir una nova fila en una taula si no existeix, o actualitzar-la si ja existeix. Aquesta operació és molt útil per evitar duplicats i mantenir la integritat de les dades.

Sintaxi de l'instrucció UPSERT

La sintaxi de l'instrucció UPSERT pot variar lleugerament segons el sistema de gestió de bases de dades (SGBD) que estiguis utilitzant. A continuació, es mostra la sintaxi general per a SQL Server, PostgreSQL i MySQL.

SQL Server

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1,
               target.column2 = source.column2
WHEN NOT MATCHED THEN
    INSERT (column1, column2)
    VALUES (source.column1, source.column2);

PostgreSQL

INSERT INTO target_table (id, column1, column2)
VALUES (1, 'value1', 'value2')
ON CONFLICT (id)
DO UPDATE SET column1 = EXCLUDED.column1,
              column2 = EXCLUDED.column2;

MySQL

INSERT INTO target_table (id, column1, column2)
VALUES (1, 'value1', 'value2')
ON DUPLICATE KEY UPDATE column1 = VALUES(column1),
                        column2 = VALUES(column2);

Exemple pràctic

Suposem que tenim una taula products amb les següents columnes: product_id, product_name i price. Volem inserir un nou producte o actualitzar el preu si el producte ja existeix.

SQL Server

MERGE INTO products AS target
USING (VALUES (1, 'Laptop', 1200)) AS source (product_id, product_name, price)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET target.product_name = source.product_name,
               target.price = source.price
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price)
    VALUES (source.product_id, source.product_name, source.price);

PostgreSQL

INSERT INTO products (product_id, product_name, price)
VALUES (1, 'Laptop', 1200)
ON CONFLICT (product_id)
DO UPDATE SET product_name = EXCLUDED.product_name,
              price = EXCLUDED.price;

MySQL

INSERT INTO products (product_id, product_name, price)
VALUES (1, 'Laptop', 1200)
ON DUPLICATE KEY UPDATE product_name = VALUES(product_name),
                        price = VALUES(price);

Exercicis pràctics

Exercici 1

Crea una taula employees amb les columnes employee_id, employee_name i salary. Escriu una instrucció UPSERT per inserir un nou empleat o actualitzar el salari si l'empleat ja existeix.

Solució

SQL Server

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

MERGE INTO employees AS target
USING (VALUES (1, 'John Doe', 50000)) AS source (employee_id, employee_name, salary)
ON target.employee_id = source.employee_id
WHEN MATCHED THEN
    UPDATE SET target.employee_name = source.employee_name,
               target.salary = source.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, employee_name, salary)
    VALUES (source.employee_id, source.employee_name, source.salary);

PostgreSQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, employee_name, salary)
VALUES (1, 'John Doe', 50000)
ON CONFLICT (employee_id)
DO UPDATE SET employee_name = EXCLUDED.employee_name,
              salary = EXCLUDED.salary;

MySQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, employee_name, salary)
VALUES (1, 'John Doe', 50000)
ON DUPLICATE KEY UPDATE employee_name = VALUES(employee_name),
                        salary = VALUES(salary);

Exercici 2

Tens una taula inventory amb les columnes item_id, item_name i quantity. Escriu una instrucció UPSERT per inserir un nou article o actualitzar la quantitat si l'article ja existeix.

Solució

SQL Server

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100),
    quantity INT
);

MERGE INTO inventory AS target
USING (VALUES (1, 'Keyboard', 50)) AS source (item_id, item_name, quantity)
ON target.item_id = source.item_id
WHEN MATCHED THEN
    UPDATE SET target.item_name = source.item_name,
               target.quantity = source.quantity
WHEN NOT MATCHED THEN
    INSERT (item_id, item_name, quantity)
    VALUES (source.item_id, source.item_name, source.quantity);

PostgreSQL

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100),
    quantity INT
);

INSERT INTO inventory (item_id, item_name, quantity)
VALUES (1, 'Keyboard', 50)
ON CONFLICT (item_id)
DO UPDATE SET item_name = EXCLUDED.item_name,
              quantity = EXCLUDED.quantity;

MySQL

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100),
    quantity INT
);

INSERT INTO inventory (item_id, item_name, quantity)
VALUES (1, 'Keyboard', 50)
ON DUPLICATE KEY UPDATE item_name = VALUES(item_name),
                        quantity = VALUES(quantity);

Errors comuns i consells

Claus primàries o úniques: Assegura't que la taula tingui una clau primària o un índex únic en la columna que utilitzes per a la coincidència. Sense això, l'operació UPSERT no funcionarà correctament.
Conflictes de dades: Si hi ha conflictes de dades, assegura't de definir clarament què s'ha d'actualitzar. Utilitza les paraules clau EXCLUDED (PostgreSQL) o VALUES (MySQL) per referir-te als valors que s'intenten inserir.
Optimització: L'ús d'UPSERT pot ser costós en termes de rendiment si es fa en grans volums de dades. Considera l'ús d'índexs adequats per millorar el rendiment.

Conclusió

L'instrucció UPSERT (MERGE) és una eina poderosa per gestionar la inserció i actualització de dades en una taula de manera eficient. Comprendre la seva sintaxi i aplicació en diferents SGBD és essencial per mantenir la integritat de les dades i optimitzar les operacions de bases de dades. Amb els exemples i exercicis proporcionats, hauràs adquirit una comprensió sòlida de com utilitzar aquesta instrucció en els teus projectes.

Instrucció UPSERT (MERGE)

Introducció

Sintaxi de l'instrucció UPSERT

SQL Server

PostgreSQL

MySQL

Exemple pràctic

SQL Server

PostgreSQL

MySQL

Exercicis pràctics

Exercici 1

Solució

SQL Server

PostgreSQL

MySQL

Exercici 2

Solució

SQL Server

PostgreSQL

MySQL

Errors comuns i consells

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes niades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL en la pràctica

Mòdul 12: Projecte final