Instrução MySQL INSERT ON DUPLICATE KEY UPDATE

Resumo : neste tutorial, você aprenderá como usar INSERT ON DUPLICATE KEY UPDATEa instrução MySQL para inserir dados em uma tabela ou atualizar dados se ocorrer um erro de violação de chave duplicada.

Observe que este tutorial é relevante para MySQL 8.0.19 ou posterior.

Introdução à instrução MySQL INSERT ON DUPLICATE KEY UPDATE

No MySQL, a INSERT ON DUPLICATE KEY UPDATEinstrução permite inserir novas linhas em uma tabela .

Se ocorrer uma violação de chave duplicada, você poderá usar a INSERT ON DUPLICATE KEY UPDATEinstrução para atualizar as linhas existentes em vez de gerar um erro.

Esta INSERT ON DUPLICATE KEY UPDATEinstrução é útil quando você lida com restrições exclusivas ou chaves primárias .

Aqui está a sintaxe da INSERT ON DUPLICATE KEY UPDATEinstrução:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
   column1 = new_value1, 
   column2 = new_value2, 
   ...;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

• INSERT INTO table_name: Especifique o nome da tabela após as INSERT INTOpalavras-chave.
• (column1, column2, ...): Liste as colunas da tabela onde você deseja inserir os dados
• VALUES(...): Forneça valores a serem inseridos nas colunas correspondentes
• ON DUPLICATE KEY UPDATE: especifique a ação a ser tomada se ocorrer uma violação de chave duplicada.
• column1 = new_value1, column2=new_value2: defina como as linhas existentes devem ser atualizadas se uma chave duplicada for encontrada.

A instrução retorna o número de linhas afetadas com base na ação que executa:

• 1 é retornado quando uma nova linha é inserida.
• 2 é retornado quando uma linha existente é atualizada.
• 0 é retornado quando nenhuma alteração é feita em uma linha existente.

Aliases de linha

O MySQL permite que você defina um alias de linha para a linha inserida usando a cláusula AS alias_nameafter VALUES.

Então, você pode usar o alias dentro da ON DUPLICATE KEY UPDATEcláusula para fazer referência aos valores da linha inserida.

Esta é a sintaxe para definir um alias de linha:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
AS new_data -- Row alias
ON DUPLICATE KEY UPDATE
  column1 = new_data.column1,
  column2 = new_data.column2 + 1;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aliases de coluna

O MySQL também permite atribuir aliases às colunas para evitar ambiguidade, especialmente com nomes de colunas longos:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (...)
AS new(alias1, alias2, alias3, ...)
ON DUPLICATE KEY UPDATE
  column1 = alias2 + alias3;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Exemplos de MySQL INSERT ON DUPLICATE KEY UPDATE

Vejamos alguns exemplos de uso do INSERT ON DUPLICATE KEY UPDATE para entender como ele funciona.

Criaremos uma employeestabela para demonstração:

CREATE TABLE employees(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   age INT NOT NULL,
   salary DECIMAL(10,2) NOT NULL,
   bonus DECIMAL(10,2) DEFAULT 0
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

1) Exemplo de uso de alias de linha

Primeiro, insira uma nova linha na employeestabela:

INSERT INTO employees(id, name, age, salary)
VALUES(1, 'Jane Doe', 25, 120000);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, recupere os dados da employeestabela:

SELECT * FROM employees;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----+----------+-----+-----------+-------+
| id | name     | age | salary    | bonus |
+----+----------+-----+-----------+-------+
|  1 | Jane Doe |  25 | 120000.00 |  0.00 |
+----+----------+-----+-----------+-------+
1 row in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Terceiro, insira uma nova linha na employeestabela ou atualize a linha existente se ocorrer uma violação de chave duplicada:

INSERT INTO employees(id, name, age, salary)
VALUES (1, 'Jane Smith', 26, 130000)

AS new
ON DUPLICATE KEY UPDATE
   name = new.name,
   age = new.age,
   salary = new.salary;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

Query OK, 2 rows affected (0.00 sec)Linguagem de código:  CSS  ( css )

Neste exemplo, usamos newcomo alias de linha. Como a linha com id 1 já existe, a instrução atualiza a linha em vez de inserir uma nova.

Na ON DUPLICATE KEY UPDATEcláusula, acessamos o novo valor especificado na VALUEScláusula por meio do alias da linha e usamos esses novos valores para atualizar as colunas name, agee .salary

Por fim, recupere os dados da employeestabela:

SELECT * FROM employees;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----+------------+-----+-----------+-------+
| id | name       | age | salary    | bonus |
+----+------------+-----+-----------+-------+
|  1 | Jane Smith |  26 | 130000.00 |  0.00 |
+----+------------+-----+-----------+-------+
1 row in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

2) Usando a instrução MySQL INSERT ON DUPLICATE KEY UPDATE para atualizar outro exemplo de coluna

Primeiro, insira uma nova linha ou atualize a nova coluna de salário e bônus, se a linha existir:

INSERT INTO employees(id, name, age, salary)
VALUES(1, 'Jane Doe', 26, 140000)
AS new
ON DUPLICATE KEY UPDATE
   salary = new.salary,
   bonus = new.salary * 0.1;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

Query OK, 2 rows affected (0.01 sec)Linguagem de código:  CSS  ( css )

Segundo, recupere os dados da employeestabela:

SELECT * FROM employees;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+----+------------+-----+-----------+----------+
| id | name       | age | salary    | bonus    |
+----+------------+-----+-----------+----------+
|  1 | Jane Smith |  26 | 140000.00 | 14000.00 |
+----+------------+-----+-----------+----------+
1 row in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Resumo

• Use a ON DUPLICATE KEY UPDATEopção da INSERTinstrução para inserir dados em uma tabela e atualizar os dados existentes se ocorrer um erro duplicado.