Update or insert with data from another table

Question

Update or insert with data from another table

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have this SQL command to update the REQUISICOES table with the data in the REQUISICOES_ATUALIZA table.

Use as a key to update the field number_requisition, but I need that if I do not find correspondencia in the REQUISICOES table, insert a new record from the REQUISICOES_ATUALIZA table;

This works to update:

UPDATE requisicoes a
JOIN requisicoes_atualiza b
SET a.projeto = b.projeto
WHERE a.numero_requisicao = b.numero_requisicao

And how would SQL be for those that do not exist?

mysql sql sql-update

asked by anonymous 30.05.2018 / 17:24

2 answers

Save form Razor plugin creating empty space below HTML when resizing screen

score 1 · Answer 1

Using NOT EXISTS . If the numero_requisição field is a declared unique, you can use INSERT ... ON DUPLICATE KEY UPDATE :

INSERT INTO requisicoes (numero_requisicao, projeto)
SELECT numero_requisicao, projeto
FROM requisicoes_atualiza ra
ON DUPLICATE KEY UPDATE projeto = ra.projeto

score 0 · Answer 2

To insert what is not in your request table you can use the SQL below

INSERT INTO requisicoes (numero_requisicao, projeto)
SELECT b.numero_requisicao, b.projeto
FROM requisicoes a
LEFT JOIN requisicoes_atualiza b on a.numero_requisicao = b.numero_requisicao
WHERE a.numero_requisicao is null

I use this table a lot when I have this type of doubt: