ti-enxame.com

INSERIR SE NÃO EXISTE MAIS ATUALIZADO?

Eu encontrei alguns "seriam" soluções para o clássico "Como faço para inserir um novo registro ou atualizar um, se já existir", mas não consigo qualquer um deles para trabalhar no SQLite.

Eu tenho uma tabela definida da seguinte forma:

CREATE TABLE Book 
ID     INTEGER PRIMARY KEY AUTOINCREMENT,
Name   VARCHAR(60) UNIQUE,
TypeID INTEGER,
Level  INTEGER,
Seen   INTEGER

O que eu quero fazer é adicionar um registro com um nome exclusivo. Se o nome já existir, quero modificar os campos.

Alguém pode me dizer como fazer isso, por favor?

252
SparkyNZ

Dê uma olhada em http://sqlite.org/lang_conflict.html .

Você quer algo como:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values
((select ID from Book where Name = "SearchName"), "SearchName", ...);

Observe que qualquer campo que não estiver na lista de inserção será definido como NULL se a linha já existir na tabela. É por isso que há uma subseleção para a coluna ID: no caso de substituição, a instrução a configuraria para NULL e, em seguida, uma ID nova seria alocada.

Essa abordagem também pode ser usada se você quiser deixar valores de campo específicos sozinhos se a linha no caso de substituição, mas definir o campo como NULL no caso de inserção.

Por exemplo, supondo que você queira deixar Seen sozinho:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
   (select ID from Book where Name = "SearchName"),
   "SearchName",
    5,
    6,
    (select Seen from Book where Name = "SearchName"));
304
janm

Você deve usar o comando INSERT OR IGNORE seguido por um comando UPDATE: No exemplo a seguir, name é uma chave primária:

INSERT OR IGNORE INTO my_table (name, age) VALUES ('Karen', 34)
UPDATE my_table SET age = 34 WHERE name='Karen'

O primeiro comando irá inserir o registro. Se o registro existir, ele ignorará o erro causado pelo conflito com uma chave primária existente.

O segundo comando atualizará o registro (que agora definitivamente existe)

75
moshik

Você precisa definir uma restrição na tabela para acionar um " conflict ", o qual você resolve fazendo uma substituição:

CREATE TABLE data   (id INTEGER PRIMARY KEY, event_id INTEGER, track_id INTEGER, value REAL);
CREATE UNIQUE INDEX data_idx ON data(event_id, track_id);

Então você pode emitir:

INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 2, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 5);

O "SELECT * FROM data" lhe dará:

2|2|2|3.0
3|1|2|5.0

Observe que o data.id é "3" e não "1" porque REPLACE faz um DELETE e INSERT, não um UPDATE. Isso também significa que você deve garantir que você defina todas as colunas necessárias ou obterá valores NULL inesperados.

61
gaspard

Em primeiro lugar atualizá-lo. Se contagem da linha afetada = 0, insira-o. É o mais fácil e adequado para todos RDBMS.

32
Burçin

INSERT OR REPLACE irá substituir os outros campos (TypeID, Level) pelo valor padrão.

INSERT OR REPLACE INTO book(id, name) VALUES(1001, 'Programming')

Eu estou usando isso

INSERT OR IGNORE INTO book(id) VALUES(1001);
UPDATE book SET name = 'Programming' WHERE id = 1001;

Você também pode usar

INSERT OR REPLACE INTO book (id, name) 
VALUES (1001, 'Programming',
  (SELECT typeid FROM book WHERE id = 1001),
  (SELECT level FROM book WHERE id = 1001),
)

mas acho que o primeiro método é mais fácil de ler

16
Steely Wing

Se você não tem chave primária, você pode inserir se não existir, então faça uma atualização. A tabela deve conter pelo menos uma entrada antes de usar isso.

INSERT INTO Test 
   (id, name)
   SELECT 
      101 as id, 
      'Bob' as name
   FROM Test
       WHERE NOT EXISTS(SELECT * FROM Test WHERE id = 101 and name = 'Bob') LIMIT 1;

Update Test SET id='101' WHERE name='Bob';
5
matt

psert é o que você quer. A sintaxe UPSERT foi adicionada ao SQLite com a versão 3.24.0 (2018-06-04).

CREATE TABLE phonebook2(
  name TEXT PRIMARY KEY,
  phonenumber TEXT,
  validDate DATE
);

INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate;

Esteja avisado que neste ponto a palavra atual "UPSERT" não faz parte da sintaxe upsert.

A sintaxe correta é

INSERT INTO ... ON CONFLICT(...) DO UPDATE SET...

e se você estiver fazendo INSERT INTO SELECT ..., seu select precisa de pelo menos WHERE true para resolver a ambiguidade do analisador sobre o token ON com a sintaxe de junção.

Esteja avisado que INSERT OR REPLACE... irá deletar o registro antes de inserir um novo caso seja necessário substituí-lo, o que pode ser ruim se você tiver cascatas de chave estrangeira ou outros gatilhos de exclusão.

3
ComradeJoecool

Eu acredito que você quer UPSERT .

"INSERT OR REPLACE" sem o truque adicional nessa resposta irá redefinir quaisquer campos que você não especificar para NULL ou outro valor padrão. (Esse comportamento de INSERT OR REPLACE é diferente de UPDATE; é exatamente como INSERT, porque na verdade é INSERT; entretanto, se o que você queria é UPDATE-if-exists, você provavelmente quer a semântica UPDATE e será desagradavelmente surpreendido pelo resultado real.)

O truque da implementação da UPSERT sugerida é basicamente usar INSERT OR REPLACE, mas especificar todos os campos, usando cláusulas SELECT incorporadas para recuperar o valor atual dos campos que você não deseja alterar.

3
metamatt

Eu acho que vale a pena ressaltar que pode haver algum comportamento inesperado aqui se você não entender completamente como PRIMARY KEY e UNIQUE interagem.

Por exemplo, se você quiser inserir um registro apenas se o campo NAME não estiver sendo usado, e se for, você deseja que uma exceção de restrição seja acionada para informar você, então INSERT OR REPLACE não lançará e exceção e, em vez disso, resolverá o UNIQUE restringe-se substituindo o registro conflitante (o registro existente com o mesmo NAME). Gaspard demonstra isso muito bem em sua resposta acima.

Se você quer que uma exceção de restrição seja disparada, você tem que usar uma instrução INSERT, e contar com um separado UPDATE comando para atualizar o registro uma vez que você sabe que o nome não foi escolhido.

1
Matt Matthias