SQLite – UPSERT * não * INSERT ou REPLACE

http://en.wikipedia.org/wiki/Upsert

Inserir atualização do procedimento armazenado no SQL Server

Existe alguma maneira inteligente de fazer isso no SQLite que eu não tenha pensado?

Basicamente eu quero atualizar três de quatro colunas, se o registro existir, se não existir, quero INSERIR o registro com o valor padrão (NUL) para a quarta coluna.

O ID é uma chave primária, portanto, sempre haverá um registro para o UPSERT.

(Eu estou tentando evitar a sobrecarga de SELECT, a fim de determinar se eu preciso de UPDATE ou INSERT, obviamente)

Sugestões?


Não posso confirmar essa syntax no site SQLite para TABLE CREATE. Eu não criei uma demonstração para testá-lo, mas não parece ser suportado ..

Se fosse, eu tenho três colunas para parecer com:

CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, Blob1 BLOB ON CONFLICT REPLACE, Blob2 BLOB ON CONFLICT REPLACE, Blob3 BLOB ); 

mas os dois primeiros blobs não causam um conflito, apenas o ID iria Então eu assumo o Blob1 e o Blob2 não seria substituído (como desejado)


ATUALIZAÇÕES no SQLite quando os dados de binding são uma transação completa, o que significa que cada linha enviada a ser atualizada requer: instruções Prepare / Bind / Step / Finalize, ao contrário do INSERT, que permite o uso da function de redefinição.

A vida de um object de declaração é algo assim:

  1. Crie o object usando sqlite3_prepare_v2 ()
  2. Vincule valores aos parâmetros do host usando sqlite3_bind_ interfaces.
  3. Execute o SQL chamando sqlite3_step ()
  4. Redefina a instrução usando sqlite3_reset () e volte ao passo 2 e repita.
  5. Destrua o object de instrução usando sqlite3_finalize ().

UPDATE Eu estou supondo que é lento em comparação com INSERT, mas como se compara a SELECT usando a chave primária?

Talvez eu deva usar o select para ler a quarta coluna (Blob3) e então usar REPLACE para escrever um novo registro misturando a 4ª coluna original com os novos dados para as 3 primeiras colunas?

Assumindo 3 colunas na tabela. ID, NAME, ROLE


BAD: Isso irá inserir ou replace todas as colunas com novos valores para ID = 1:

 INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, 'John Foo', 'CEO'); 

BAD: Isso irá inserir ou replace 2 das colunas … a coluna NAME será definida como NULL ou o valor padrão:

 INSERT OR REPLACE INTO Employee (id, role) VALUES (1, 'code monkey'); 

BOA: Isso atualizará 2 das colunas. Quando ID = 1 existir, o NAME não será afetado. Quando ID = 1 não existe, o nome será padrão (NULL).

 INSERT OR REPLACE INTO Employee (id, role, name) VALUES ( 1, 'code monkey', (SELECT name FROM Employee WHERE id = 1) ); 

Isso atualizará 2 das colunas. Quando ID = 1 existir, o ROLE não será afetado. Quando ID = 1 não existir, a function será definida como ‘Benchwarmer’ em vez do valor padrão.

 INSERT OR REPLACE INTO Employee (id, name, role) VALUES ( 1, 'Susan Bar', COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer') ); 

INSERT OR REPLACE não é equivalente a “UPSERT”.

Digamos que eu tenha a tabela Employee com os campos id, name e role:

 INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO") INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey") 

Boom, você perdeu o nome do funcionário número 1. O SQLite substituiu-o por um valor padrão.

A saída esperada de uma UPSERT seria alterar a function e manter o nome.

A resposta de Eric B é OK se você quiser preservar apenas uma ou talvez duas colunas da linha existente. Se você quiser preservar muitas colunas, fica muito complicado.

Aqui está uma abordagem que irá escalar bem para qualquer quantidade de colunas em ambos os lados. Para ilustrar, vou assumir o seguinte esquema:

  CREATE TABLE page ( id INTEGER PRIMARY KEY, name TEXT UNIQUE, title TEXT, content TEXT, author INTEGER NOT NULL REFERENCES user (id), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 

Note em particular que name é a chave natural da linha – id é usado apenas para foreign keys, então o ponto é que o SQLite escolha o valor da ID ao inserir uma nova linha. Mas, ao atualizar uma linha existente com base em seu name , quero que continue a ter o antigo valor de ID (obviamente!).

Eu UPSERT um verdadeiro UPSERT com a seguinte construção:

  WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) ) INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT old.id, new.name, new.title, old.content, new.author FROM new LEFT JOIN page AS old ON new.name = old.name; 

A forma exata dessa consulta pode variar um pouco. A chave é o uso de INSERT SELECT com uma junit externa esquerda, para unir uma linha existente aos novos valores.

Aqui, se uma linha não existia anteriormente, o old.id será NULL e o SQLite atribuirá um ID automaticamente, mas se já houver essa linha, o old.id terá um valor real e será reutilizado. Qual é exatamente o que eu queria.

Na verdade isso é muito flexível. Note como a coluna ts está completamente ausente em todos os lados – porque ela tem um valor padrão, o SQLite apenas fará a coisa certa em qualquer caso, então eu não tenho que cuidar disso sozinho.

Você também pode include uma coluna nos lados new e old e usar, por exemplo, COALESCE(new.content, old.content) no SELECT externo para dizer “insira o novo conteúdo se houver algum, caso contrário, mantenha o conteúdo antigo” – por exemplo, se você estiver usando uma consulta fixa e estiver vinculando os novos valores a espaços reservados.

Se você está geralmente fazendo atualizações eu faria ..

  1. Comece uma transação
  2. Faça a atualização
  3. Verifique o número de linhas
  4. Se for 0, insira
  5. Commit

Se você está geralmente fazendo inserções eu faria

  1. Comece uma transação
  2. Tente uma inserção
  3. Verificar se há erro de violação de chave primária
  4. se tivermos um erro, faça a atualização
  5. Commit

Desta forma, você evita o select e você é transacionalmente som no Sqlite.

Eu percebo que este é um thread antigo, mas eu tenho trabalhado em sqlite3 ultimamente e surgiu com este método que melhor atende às minhas necessidades de gerar dinamicamente consultas parametrizadas:

 insert or ignore into (, , , ...) values(, , , ...); update 
set =, =, ... where changes()=0 and =;

Ainda é duas consultas com uma cláusula where na atualização, mas parece fazer o truque. Eu também tenho essa visão em mente que o sqlite pode otimizar a instrução de atualização se a chamada para changes () for maior que zero. Realmente ou não, isso está além do meu conhecimento, mas um homem pode sonhar, não pode? 😉

Para pontos de bônus, você pode append esta linha que retorna o ID da linha, seja ela uma linha recém-inserida ou uma linha existente.

 select case changes() WHEN 0 THEN last_insert_rowid() else  end; 

2018-05-18 STOP PRESS.

Suporte a UPSERT no SQLite! A syntax do UPSERT foi adicionada ao SQLite com a versão 3.24.0 (pendente)!

UPSERT é uma adição especial de syntax ao INSERT que faz com que o INSERT se comporte como um UPDATE ou um no-op caso o INSERT viole uma restrição de unicidade. O UPSERT não é um SQL padrão. O UPSERT no SQLite segue a syntax estabelecida pelo PostgreSQL.

insira a descrição da imagem aqui

Eu sei que estou atrasado para a festa, mas ….

 UPDATE employee SET role = 'code_monkey', name='fred' WHERE id = 1; INSERT OR IGNORE INTO employee(id, role, name) values (1, 'code monkey', 'fred'); 

Então, ele tenta atualizar, se o registro estiver lá, a inserção não será executada.

alternativamente:

Outra maneira completamente diferente de fazer isso é: Em meu aplicativo, configurei meu rowID em memory para ser long.MaxValue quando eu criar a linha na memory. (MaxValue nunca será usado como um ID que você não vai viver o suficiente …. Então, se rowID não é esse valor, então ele já deve estar no database, então precisa de um UPDATE se for MaxValue, então ele precisa de uma inserção. Isso só é útil se você puder acompanhar os rowIDs no seu aplicativo.

Aqui está uma solução que realmente é um UPSERT (UPDATE ou INSERT) em vez de um INSERT OR REPLACE (que funciona de forma diferente em muitas situações).

Funciona assim:
1. Tente atualizar se um registro com o mesmo Id existir.
2. Se a atualização não tiver alterado nenhuma linha ( NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0) ), insira o registro.

Portanto, um registro existente foi atualizado ou uma inserção será executada.

O detalhe importante é usar a function SQL changes () para verificar se a instrução de atualização atingiu qualquer registro existente e somente executar a instrução insert se ele não atingiu nenhum registro.

Uma coisa a ser mencionada é que a function changes () não retorna as alterações realizadas pelos gatilhos de nível mais baixo (veja http://sqlite.org/lang_corefunc.html#changes ), então lembre-se de levar isso em consideração.

Aqui está o SQL …

Atualização de teste:

 --Create sample table and records (and drop the table if it already exists) DROP TABLE IF EXISTS Contact; CREATE TABLE [Contact] ( [Id] INTEGER PRIMARY KEY, [Name] TEXT ); INSERT INTO Contact (Id, Name) VALUES (1, 'Mike'); INSERT INTO Contact (Id, Name) VALUES (2, 'John'); -- Try to update an existing record UPDATE Contact SET Name = 'Bob' WHERE Id = 2; -- If no record was changed by the update (meaning no record with the same Id existed), insert the record INSERT INTO Contact (Id, Name) SELECT 2, 'Bob' WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0); --See the result SELECT * FROM Contact; 

Inserção de teste:

 --Create sample table and records (and drop the table if it already exists) DROP TABLE IF EXISTS Contact; CREATE TABLE [Contact] ( [Id] INTEGER PRIMARY KEY, [Name] TEXT ); INSERT INTO Contact (Id, Name) VALUES (1, 'Mike'); INSERT INTO Contact (Id, Name) VALUES (2, 'John'); -- Try to update an existing record UPDATE Contact SET Name = 'Bob' WHERE Id = 3; -- If no record was changed by the update (meaning no record with the same Id existed), insert the record INSERT INTO Contact (Id, Name) SELECT 3, 'Bob' WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0); --See the result SELECT * FROM Contact; 

Expandindo a resposta de Aristóteles, você pode SELECT de uma tabela ‘singleton’ fictícia (uma tabela de sua própria criação com uma única linha). Isso evita alguma duplicação.

Também mantive o exemplo portable em MySQL e SQLite e usei uma coluna ‘date_added’ como exemplo de como você poderia definir uma coluna apenas na primeira vez.

  REPLACE INTO page ( id, name, title, content, author, date_added) SELECT old.id, "about", "About this site", old.content, 42, IFNULL(old.date_added,"21/05/2013") FROM singleton LEFT JOIN page AS old ON old.name = "about"; 

A melhor abordagem que eu sei é fazer uma atualização, seguida por uma inserção. A “sobrecarga de um select” é necessária, mas não é um fardo terrível, já que você está pesquisando na chave primária, o que é rápido.

Você deve poder modificar as instruções abaixo com seus nomes de tabela e campo para fazer o que quiser.

 --first, update any matches UPDATE DESTINATION_TABLE DT SET MY_FIELD1 = ( SELECT MY_FIELD1 FROM SOURCE_TABLE ST WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY ) ,MY_FIELD2 = ( SELECT MY_FIELD2 FROM SOURCE_TABLE ST WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY ) WHERE EXISTS( SELECT ST2.PRIMARY_KEY FROM SOURCE_TABLE ST2 ,DESTINATION_TABLE DT2 WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY ); --second, insert any non-matches INSERT INTO DESTINATION_TABLE( MY_FIELD1 ,MY_FIELD2 ) SELECT ST.MY_FIELD1 ,NULL AS MY_FIELD2 --insert NULL into this field FROM SOURCE_TABLE ST WHERE NOT EXISTS( SELECT DT2.PRIMARY_KEY FROM DESTINATION_TABLE DT2 WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY ); 

Se alguém quiser ler minha solução para o SQLite no Cordova, recebi este método js genérico graças à resposta @david acima.

 function addOrUpdateRecords(tableName, values, callback) { get_columnNames(tableName, function (data) { var columnNames = data; myDb.transaction(function (transaction) { var query_update = ""; var query_insert = ""; var update_string = "UPDATE " + tableName + " SET "; var insert_string = "INSERT INTO " + tableName + " SELECT "; myDb.transaction(function (transaction) { // Data from the array [[data1, ... datan],[()],[()]...]: $.each(values, function (index1, value1) { var sel_str = ""; var upd_str = ""; var remoteid = ""; $.each(value1, function (index2, value2) { if (index2 == 0) remoteid = value2; upd_str = upd_str + columnNames[index2] + "='" + value2 + "', "; sel_str = sel_str + "'" + value2 + "', "; }); sel_str = sel_str.substr(0, sel_str.length - 2); sel_str = sel_str + " WHERE NOT EXISTS(SELECT changes() AS change FROM "+tableName+" WHERE change <> 0);"; upd_str = upd_str.substr(0, upd_str.length - 2); upd_str = upd_str + " WHERE remoteid = '" + remoteid + "';"; query_update = update_string + upd_str; query_insert = insert_string + sel_str; // Start transaction: transaction.executeSql(query_update); transaction.executeSql(query_insert); }); }, function (error) { callback("Error: " + error); }, function () { callback("Success"); }); }); }); } 

Então, primeiro pegue os nomes das colunas com esta function:

 function get_columnNames(tableName, callback) { myDb.transaction(function (transaction) { var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'"; transaction.executeSql(query_exec, [], function (tx, results) { var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); ///// RegEx var columnNames = []; for (i in columnParts) { if (typeof columnParts[i] === 'string') columnNames.push(columnParts[i].split(" ")[0]); }; callback(columnNames); }); }); } 

Em seguida, construa as transactions programaticamente.

“Valores” é uma matriz que você deve construir antes e representa as linhas que você deseja inserir ou atualizar na tabela.

“remoteid” é o id que eu usei como referência, já que estou sincronizando com meu servidor remoto.

Para o uso do plug-in do SQLite Cordova, consulte o link oficial

A partir da versão 3.24.0, o UPSERT é suportado pelo SQLite.

Da documentação :

UPSERT é uma adição especial de syntax ao INSERT que faz com que o INSERT se comporte como um UPDATE ou um no-op caso o INSERT viole uma restrição de unicidade. O UPSERT não é um SQL padrão. O UPSERT no SQLite segue a syntax estabelecida pelo PostgreSQL. A syntax do UPSERT foi adicionada ao SQLite com a versão 3.24.0 (pendente).

Um UPSERT é uma instrução INSERT comum que é seguida pela cláusula ON CONFLICT

insira a descrição da imagem aqui

Fonte da imagem: https://www.sqlite.org/images/syntax/upsert-clause.gif

Acho que isso pode ser o que você está procurando: na cláusula CONFLICT .

Se você definir sua tabela assim:

 CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, field1 TEXT ); 

Agora, se você fizer um INSERT com um id que já existe, o SQLite faz automagicamente o UPDATE ao invés do INSERT.

H …

Esse método remixa alguns dos outros methods da resposta para essa questão e incorpora o uso de CTE (Common Table Expressions). Vou apresentar a consulta e explicar por que fiz o que fiz.

Eu gostaria de mudar o sobrenome do funcionário 300 para DAVIS se houver um funcionário 300. Caso contrário, adicionarei um novo funcionário.

Nome da Tabela: funcionários Colunas: id, first_name, last_name

A consulta é:

 INSERT OR REPLACE INTO employees (employee_id, first_name, last_name) WITH registered_employees AS ( --CTE for checking if the row exists or not SELECT --this is needed to ensure that the null row comes second * FROM ( SELECT --an existing row * FROM employees WHERE employee_id = '300' UNION SELECT --a dummy row if the original cannot be found NULL AS employee_id, NULL AS first_name, NULL AS last_name ) ORDER BY employee_id IS NULL --we want nulls to be last LIMIT 1 --we only want one row from this statement ) SELECT --this is where you provide defaults for what you would like to insert registered_employees.employee_id, --if this is null the SQLite default will be used COALESCE(registered_employees.first_name, 'SALLY'), 'DAVIS' FROM registered_employees ; 

Basicamente, eu usei o CTE para reduzir o número de vezes que a instrução select deve ser usada para determinar os valores padrão. Como este é um CTE, apenas selecionamos as colunas que queremos na tabela e a instrução INSERT usa isso.

Agora você pode decidir quais padrões deseja usar, substituindo os nulos, na function COALESCE, com quais valores devem ser.

Você pode realmente fazer um upsert no SQLite, ele só parece um pouco diferente do que você está acostumado. Seria algo parecido com:

 INSERT INTO table name (column1, column2) VALUES ("value12", "value2") WHERE id = 123 ON CONFLICT DO UPDATE SET column1 = "value1", column2 = "value2" WHERE id = 123 

Seguindo Aristóteles Pagaltzis e a idéia de COALESCE da resposta de Eric B , aqui está uma opção de atualização para atualizar apenas algumas colunas ou inserir linha completa se ela não existir.

Nesse caso, imagine que o título e o conteúdo devem ser atualizados, mantendo os outros valores antigos quando existentes e inserindo os fornecidos quando o nome não for encontrado:

NOTA id é forçado a ser NULL quando INSERT como é suposto ser autoincrement. Se for apenas uma chave primária gerada, então COALESCE também pode ser usado (veja o comentário de Aristóteles Pagaltzis ).

 WITH new (id, name, title, content, author) AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) ) INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT old.id, COALESCE(old.name, new.name), new.title, new.content, COALESCE(old.author, new.author) FROM new LEFT JOIN page AS old ON new.name = old.name; 

Assim, a regra geral seria, se você quiser manter valores antigos, use COALESCE , quando quiser atualizar valores, use new.fieldname

Tendo acabado de ler este tópico e ficado desapontado por não ter sido fácil apenas para este “UPSERT”, eu investiguei mais …

Você pode fazer isto diretamente e facilmente no SQLITE.

Em vez de usar: INSERT INTO

Use: INSERT OR REPLACE INTO

Isso faz exatamente o que você quer fazer!

 SELECT COUNT(*) FROM table1 WHERE id = 1; 

if COUNT(*) = 0

 INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3); 

else if COUNT(*) > 0

 UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;