Upserting em access ao MS

Eu preciso escrever uma consulta SQL para o MS-Access 2000 para que uma linha seja atualizada se existir, mas inserida se não existir. (Eu acredito que isso é chamado de “upsert“)

ou seja

Se a linha existe …

UPDATE Table1 SET (...) WHERE Column1='SomeValue' 

Se não existe …

 INSERT INTO Table1 VALUES (...) 

Isso pode ser feito em uma consulta?

Eu normalmente executo a instrução insert primeiro e, em seguida, verifico se o erro 3022 ocorreu, o que indica que a linha já existe. Então, algo assim:

 On Error Resume Next CurrentDb.Execute "INSERT INTO Table1 (Fields) VALUES (Data)", dbFailOnError If Err.Number = 3022 Then Err.Clear CurrentDb.Execute "UPDATE Table1 SET (Fields = Values) WHERE Column1 = 'SomeValue'", dbFailOnError ElseIf Err.Number <> 0 Then 'Handle the error here Err.Clear End If 

Edit1:
Eu quero mencionar que o que eu postei aqui é uma solução muito comum, mas você deve estar ciente de que planejar erros e usá-los como parte do stream normal do seu programa é geralmente considerado uma má idéia, especialmente se existem outras maneiras. de alcançar os mesmos resultados. Obrigado a RolandTumble por apontar isso.

Supondo um índice exclusivo na Coluna1 , você pode usar uma expressão DCount para determinar se você tem zero ou uma linha com Coluna1 = ‘SomeValue’ . Em seguida, INSERT ou UPDATE base nessa contagem.

 If DCount("*", "Table1", "Column1 = 'SomeValue'") = 0 Then Debug.Print "do INSERT" Else Debug.Print "do UPDATE" End If 

Eu prefiro essa abordagem para tentar primeiro um INSERT , capturar o erro de violação de chave 3022 e fazer um UPDATE em resposta ao erro. No entanto, não posso reivindicar enormes benefícios da minha abordagem. Se a sua tabela include um campo de numeração automática, evitar um INSERT falha impediria que você gastasse desnecessariamente o próximo valor de numeração automática. Eu também posso evitar a construção de uma seqüência de caracteres INSERT quando não é necessário. O Access Cookbook me disse que a concatenação de strings é uma operação moderadamente cara no VBA, portanto, procuro oportunidades para evitar a criação de strings, a menos que elas sejam realmente necessárias. Essa abordagem também evitará a criação de um bloqueio para um INSERT desnecessário.

No entanto, nenhuma dessas razões pode ser muito atraente para você. E com toda honestidade, acho que minha preferência neste caso pode ser sobre o que “parece certo” para mim. Concordo com este comentário de @ David-W-Fenton a uma pergunta anterior sobre estouro de pilha : “É melhor escrever seu SQL para não tentar acrescentar valores que já existem – isto é, impedir que o erro aconteça no primeiro colocar em vez de depender do mecanismo de database para salvá-lo de si mesmo “.

Você pode simular um upsert em um Access usando uma consulta UPDATE com um LEFT JOIN .

 update b left join a on b.id=a.id set a.f1=b.f1 , a.f2=b.f2 , a.f3=b.f3 

consulte: https://www.experts-exchange.com/questions/28713136/Can-I-use-the-SQL-MERGE-statement-in-a-query-in-Access-2010.html

Você não precisa pegar o erro. Em vez disso, basta executar a instrução INSERT e, em seguida, verifique

 CurrentDb.RecordsAffected 

Será ou 1 ou 0, dependendo.

Nota: Não é uma boa prática executar contra o CurrentDB. Melhor capturar o database para uma variável local:

 Dim db As DAO.Database Set db = CurrentDb db.Execute(INSERT...) If db.RecordsAffected = 0 Then db.Execute(UPDATE...) End If 

Um “upsert” é possível, se as tabelas tiverem uma chave única.

Essa dica antiga do Smart Access é uma das minhas favoritas:

Atualizar e append registros com uma consulta

Alan Biggs

Você sabia que pode usar uma consulta de atualização no Access para atualizar e adicionar registros ao mesmo tempo? Isso é útil se você tiver duas versões de uma tabela, tblOld e tblNew, e quiser integrar as alterações de tblNew em tblOld.

Siga esses passos:

Crie uma consulta de atualização e adicione as duas tabelas. Junte as duas tabelas arrastando o campo chave de tblNew para o campo correspondente de tblOld.

  1. Clique duas vezes no relacionamento e escolha a opção de junit que inclui todos os registros de tblNew e somente aqueles que correspondem a tblOld.

  2. Selecione todos os campos do tblOld e arraste-os para a grade do QBE.

  3. Para cada campo, no tipo de célula Atualizar para em tblNew.FieldName, onde FieldName corresponde ao nome do campo de tblOld.

  4. Selecione Propriedades de Consulta no menu Visualizar e altere Registros Exclusivos para Falso. (Isso desativa a opção DISTINCTROW na visualização SQL. Se você deixar isso em diante, obterá apenas um registro em branco nos resultados, mas deseja que um registro em branco para cada novo registro seja adicionado ao tblOld.)

  5. Execute a consulta e você verá que as alterações em tblNew agora estão em tblOld.

Isso só adicionará registros ao tblOld que foram adicionados ao tblNew. Registros no tblOld que não estão presentes no tblNew ainda permanecerão no tblOld.