sqlite - 将部分复制的新记录插入到同一个表中

Question

我对 SQLite 相当陌生，并试图做一些看起来非常简单但不起作用的事情。

我想在表中插入一条OwnerID相同但数据略有不同的新记录，部分复制，所以插入记录数据；

2, 55, 新数据, DARLTY

原始记录数据在哪里；

1、55、原始数据、DARLTY

问题是我不断接近“OwnerID”：语法错误。我在想我需要在将原始记录值插入新记录之前以某种方式将它们放入变量中，但是如何？

这就是我所拥有的，显然有缺陷，TIA

    INSERT INTO MyTable
 NULL AS KeyID, OwnerID AS OwnerID, 'newdata' AS Field2, Field3 AS Field3
 WHERE Field3 LIKE '%DARL%'
 AND Field3 <> 'DARLINGTON'
;

标签： sqliteinsert

Answer 1

您缺少SELECTandFROM关键字。

所以你的查询应该/可以是：-

INSERT INTO mytable 
    SELECT null AS keyid,ownerid AS ownerid,field2 AS field2,field3 AS field3 
    FROM mytable WHERE field3 LIKE '%DARLTY%' AND field3 <> 'DARLINGTON';

虽然稍微简单一点：-

INSERT INTO mytable (ownerid,field2,field3) 
    SELECT ownerid,field2,field3 
    FROM mytable WHERE field3 LIKE '%DARLTY%' AND field3 <> 'DARLINGTON';

会做同样的事情。

例如以下（为方便起见，将 x 添加到表名中）：-

DROP TABLE IF EXISTS mytablex ;
CREATE TABLE IF NOT EXISTS mytablex (keyid INTEGER PRIMARY KEY,ownerid INTEGER,  field2 TEXT, field3 TEXT);
INSERT INTO mytablex VALUES(1,55,'newdata','DARLTY'); -- Original value
INSERT INTO mytablex (ownerid,field2,field3) 
    SELECT ownerid,field2,field3 
    FROM mytablex WHERE field3 LIKE '%DARLTY%' AND field3 <> 'DARLINGTON';
SELECT * FROM mytablex;

结果是 ：-

但是，请注意，如果您随后运行插入，那么您将插入更多行，例如第二次使用以下内容：-

INSERT INTO mytablex (ownerid,field2,field3) 
    SELECT ownerid,field2,field3 
    FROM mytablex WHERE field3 LIKE '%DARLTY%' AND field3 <> 'DARLINGTON';
SELECT * FROM mytablex;

将导致：-

如果第三次运行，则：-

为了消除这种重复，您不妨考虑使用：-

INSERT INTO mytablex (ownerid,field2,field3) SELECT ownerid,field2,field3 
    FROM mytablex 
        WHERE field3 LIKE '%DARLTY%' 
            AND field3 <> 'DARLINGTON' 
            AND (
                SELECT count() FROM mytablex WHERE field3 LIKE '%DARLTY%' AND field3 <> 'DARLINGTON'
                 ) = 1;