如果存在，更新，否则插入新记录 [英] if exists, update, else insert new record

问题描述

我正在向表格中插入值

如果该记录已经存在则替换它，如果它不存在则添加一条新记录.

if the record exists already replace it, and if it does not exist then add a new one.

到目前为止我有这个代码:

so far i have this code:

INSERT INTO table_name
VALUES (value1, value2, value3,...) where pk="some_id";

但我需要这样的东西

if not pk="some_id" exists then  INSERT INTO table_name
    VALUES (value1, value2, value3,...) where pk="some_id"; else update table_name where pk="some_id"

什么是正确的 SQL 语法?

what would be the correct SQL syntax for this?

请注意，我使用的是 sql 访问，我猜它可以是 vba 和 sql 的组合

please note that i am using sql access and that i guess it can be a combination of vba and sql

推荐答案

首先更新导入表和主表之间匹配的行.

First update rows which match between your import table and master table.

UPDATE table_name AS m
    INNER JOIN tblImport AS i
    ON m.pk = i.pk
SET
    m.field2 = i.field2,
    m.field3 = i.field3,
    m.field4 = i.field4;

然后添加主表中不存在的所有导入记录.

Then add any imported records which don't exist in the master table.

INSERT INTO table_name (
    pk,
    field2,
    field3,
    field4)
SELECT
    i.pk,
    i.field2,
    i.field3,
    i.field4
FROM
    tblImport AS i
    LEFT JOIN table_name AS m
    ON i.pk = m.pk
WHERE
    (((m.pk) Is Null));

这篇关于如果存在，更新，否则插入新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！