Inserir, ou atualizar se já existir
Uma necessidade clássica: inserir uma linha, mas se já existir uma com a mesma chave, atualizar em vez de inserir. Sem o UPSERT, o jeito seria fazer um SELECT primeiro e depois cair num INSERT ou num UPDATE — duas idas ao banco e ainda uma condição de corrida entre elas.
O UPSERT do SQLite resolve tudo numa única instrução:
Na primeira execução, a linha é inserida normalmente. Rode de novo com um preço diferente e o mesmo sku: a linha existente é atualizada no lugar. Sem duplicata, sem erro.
Anatomia do ON CONFLICT no SQLite
A estrutura completa do upsert no SQLite fica assim:
INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;
Três peças importam:
conflict_target— a coluna (ou colunas) com restriçãoUNIQUEouPRIMARY KEYonde você espera que aconteça o conflito. É a partir dela que o SQLite decide qual índice monitorar.DO UPDATE SET ...— o que alterar na linha existente quando rola um conflito. (OuDO NOTHINGpara ignorar sem reclamar.)WHEREopcional — uma condição extra que precisa ser verdadeira para o update realmente rodar.
O alvo do conflito precisa bater com uma restrição de unicidade real. ON CONFLICT(price) não vai compilar se price não for único — o SQLite não tem como detectar conflito contra nada.
DO NOTHING: inserir se não existir, senão pular
A variante mais simples. Cai bem quando você está populando dados ou registrando eventos e duplicatas devem ser ignoradas no silêncio:
O segundo INSERT esbarra no mesmo event_id e, em circunstâncias normais, levantaria o erro UNIQUE constraint failed. Com DO NOTHING, o SQLite simplesmente ignora. Sem exceção, sem linha afetada.
É aquele "insert idempotente" para o qual muita gente recorre ao INSERT OR IGNORE. O DO NOTHING do UPSERT faz exatamente o mesmo papel e ainda combina melhor com cláusulas WHERE e RETURNING.
A pseudo-tabela excluded
Quando um conflito acontece, você passa a ter duas linhas em jogo: a que já está na tabela e a nova que você tentou inserir. O SQLite oferece um jeito de se referir às duas.
- Nomes de colunas sem prefixo (
price,name) apontam para a linha já existente. excluded.colunaaponta para a linha nova que foi rejeitada.
quantity = quantity + excluded.quantity significa "a quantidade que já existe somada à nova". Depois dos dois inserts, o item A-100 fica com quantidade 8. Esse padrão — ir acumulando valores em uma linha que já existe — é um dos truques mais úteis do UPSERT.
UPSERT condicional com WHERE
O WHERE no final permite pular a atualização quando alguma condição não for satisfeita. Ele é avaliado contra a linha existente (e também pode referenciar excluded.* para os dados que estão chegando):
A nova linha traz um updated_at mais antigo, então o WHERE dá falso e o update é ignorado. A linha existente mantém o preço mais recente. Inverta as datas e o update roda. Esse é o padrão clássico de "só sobrescrever com dados mais novos".
Upsert de várias linhas no SQLite
O VALUES aceita várias linhas, e o ON CONFLICT é aplicado a cada uma de forma independente:
A-100 entra em conflito e é atualizado. Já A-200 e A-300 são novos e acabam inseridos. Em um único comando, você obtém um resultado misto de insert e update. É uma forma limpa de sincronizar um lote de registros vindos de uma fonte externa.
UPSERT vs INSERT OR REPLACE no SQLite
À primeira vista, o INSERT OR REPLACE parece fazer a mesma coisa. Só que não.
O campo notes sumiu. O INSERT OR REPLACE apagou a linha 1 por completo e inseriu uma nova no lugar — qualquer coluna que você não tenha listado voltou pra NULL ou pro valor padrão. Além disso, dispara triggers de DELETE e propaga as ON DELETE foreign keys em cascata.
Já o UPSERT preserva a linha:
notes continua intacto. Só as colunas listadas no SET foram alteradas. Use UPSERT como padrão; só recorra a INSERT OR REPLACE quando você realmente quiser a semântica de apagar e reinserir.
Múltiplos alvos de conflito
Quando uma linha pode colidir em mais de uma constraint, dá pra encadear várias cláusulas ON CONFLICT:
Vence o primeiro constraint que disparar, e é o DO UPDATE daquele branch que executa. Na prática, a maioria das tabelas tem um alvo de conflito óbvio — a chave primária ou uma única coluna UNIQUE — e dificilmente você vai precisar de mais de uma cláusula.
Armadilhas comuns
Algumas pegadinhas que pegam muita gente:
- Sem índice único compatível, não tem UPSERT. O
ON CONFLICT(col)exige quecolsejaPRIMARY KEYou tenha um constraintUNIQUE. Caso contrário, o SQLite reclama com "no such constraint". DO UPDATEnão dispara se não houver conflito. Ele é uma alternativa ao insert, não um comportamento extra. Na primeira vez que uma chave aparece, só o insert roda.excludedé somente leitura. Dá pra ler dele, mas não escrever. O alvo doSETé sempre a linha que já existe.- Rowids gerados via
INTEGER PRIMARY KEY. Se você não fornece o id, cada insert ganha um novo — não tem com o que conflitar. O UPSERT só faz sentido quando a coluna em conflito tem um valor determinístico fornecido por quem chama.
A seguir: RETURNING
O UPSERT não te diz nada sobre quais linhas foram inseridas ou atualizadas, nem como ficaram os valores finais. Pra isso, existe a cláusula RETURNING — ela devolve as linhas afetadas no próprio comando, sem precisar de um SELECT depois. É o que vem a seguir.
Perguntas frequentes
O que é UPSERT no SQLite?
UPSERT é um INSERT que vira um UPDATE (ou simplesmente não faz nada) quando violaria uma restrição UNIQUE ou PRIMARY KEY. A sintaxe é INSERT ... ON CONFLICT(coluna) DO UPDATE SET ... ou DO NOTHING. O SQLite passou a oferecer suporte a partir da versão 3.24.0, em 2018.
Para que serve a tabela excluded no UPSERT do SQLite?
excluded é uma pseudo-tabela especial que guarda a linha que você tentou inserir. Dentro do DO UPDATE SET ..., você referencia a linha já existente pelo nome da coluna e a linha rejeitada como excluded.coluna. Ou seja, SET price = excluded.price significa: 'sobrescreve o price com o valor que veio no novo INSERT'.
Qual a diferença entre INSERT OR REPLACE e UPSERT?
O INSERT OR REPLACE apaga a linha em conflito e insere uma nova no lugar — isso dispara triggers de DELETE, quebra chaves estrangeiras com ON DELETE CASCADE e ainda zera todas as colunas para os valores padrão. Já o UPSERT atualiza a linha existente no lugar, mexendo só nas colunas que você listou no SET. Use UPSERT, a não ser que você realmente queira deletar e reinserir.
Dá para fazer UPSERT de várias linhas de uma vez no SQLite?
Dá sim. INSERT INTO t(...) VALUES (...), (...), (...) ON CONFLICT(col) DO UPDATE SET ... funciona normalmente. Cada linha é avaliada individualmente contra o conflict target, e dentro do DO UPDATE o excluded aponta para a linha que causou aquele conflito específico.
