SQLite中的SQLite/ ELSE

Question

在MySQL中，

IF NOT EXISTS (SELECT mtrNo from tblMeter where mtrNo = '0000033')
    BEGIN
        insert into tblMeter (mtrNo) values ('0000033')
    END
ELSE
    BEGIN 
        update tblMeter set Name = 'A-15' where mtrNo = '0000033'
    END

就是工作。

但是在SQLite中，需要两条SQL语句。

insert into tblMeter (mtrNo, Name) select '0000033', 'A-15' where not exists (SELECT mtrNo from tblMeter where mtrNo = '0000033')
update tblMeter set Name = 'A-15'  where mtrNo = '0000033' and exists (SELECT mtrNo from tblMeter where mtrNo = '0000033')

你能把它变成一个像MySQL这样的SQL语句吗？

我知道插入或替换，但我不能使用，因为表没有唯一的键。

Answer 1

INSERT OR REPLACE和所谓的UPSERT (INSERT ... ON CONFLICT DO UPDATE)语句都依赖于唯一或主键约束的失败。虽然您自己的数据没有唯一的约束，但是sqlite为您提供了一个自动整数rowid主键。您可以使用它来利用备选insert语句的行为。

这第一条语句应该完全复制MySQL行为，因为它1)没有在插入时为Name赋值，而2)它更新了现有行的一列。您的表没有唯一的键这一事实表明，它可能有多个具有相同mtrNo值的行。嵌套查询的复杂性在于检索所有现有行和/或为新行生成一个有效的rowid值：

INSERT INTO tblMeter AS ti
       (rowid, mtrNo)
       SELECT COALESCE(t1.rowid, (SELECT max(tx.rowid) FROM tblMeter AS tx) + 1, 1) AS rid,
              val.mtrNo
              FROM (SELECT '0000033' AS mtrNo) AS val
                   LEFT JOIN tblMeter AS t1
                   ON val.mtrNo == t1.mtrNo
              WHERE true --avoid parsing ambiguity
ON CONFLICT (rowid)
DO UPDATE SET Name = 'A-15'; --Only rows with matching mtrNo will be updated

由于您提到了INSERT OR REPLACE语句，下面是该语句的工作版本。但是，这不是一回事，因为它完全取代了现有的行，而不仅仅是更新它们。这种区别是至关重要的，特别是如果表中的列多于示例代码中引用的列。此声明将删除现有数据！

不仅如此，与MySQL代码不同的是，这会将一个值插入到新行的Name列中。再多一些嵌套的子查询也许可以解决这个差异，但是当UPSERT语句已经更精确地满足了您的问题时，它就太复杂了。

INSERT OR REPLACE INTO tblMeter
       (rowid, mtrNo, Name)
       SELECT COALESCE(t1.rowid, (SELECT max(tx.rowid) FROM tblMeter AS tx) + 1, 1) AS rid,
              val.mtrNo,
              val.Name
              FROM (SELECT '0000033' AS mtrNo, 'A-15' AS Name) AS val
                   LEFT JOIN tblMeter AS t1
                   ON val.mtrNo == t1.mtrNo;

Answer 2

只要您有一个足够新的SQLite版本，就可以使用所谓的"UPSERT“构造：

UPSERT在SQLite中遵循PostgreSQL建立的语法。

在SQLite中添加了UPSERT语法，版本为3.24.0 (2018-06-04)

注意，没有UPSERT关键字。

有关详细信息，请参阅https://www.sqlite.org/lang_UPSERT.html