sqlite “SQL逻辑错误或缺少数据库”-插入多行时出错

eeq64g8w 于 2022-11-14 发布在 SQLite

关注(0)|答案(3)|浏览(189)

查询：

INSERT INTO "Track"
SELECT "Leonard Collections" AS "Album",
       "Instrumental" AS "Artist",
       "00:02:59.3800000" AS "Duration",
       "1/1/0001 12:00:00 AM" AS "ReleasedDate",
       "If You Love Me" AS "Title",
       "False" AS "IsPlayableOnLocal"
UNION
SELECT "Leonard Collections",
       "Instrumental",
       "00:02:56.6930000",
       "1/1/0001 12:00:00 AM",
       "Espoir",
       "False",
UNION
SELECT "Leonard Collections",
       "Instrumental",
       "00:03:51.6770000",
       "1/1/0001 12:00:00 AM",
       "Don't Cry For My Argentina",
       "False"

错误：
SQL逻辑错误或缺少数据库
Near“Union”：语法错误
表：

CREATE TABLE Track 
(
    ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , 
    Album VARCHAR(100) NULL , 
    Artist VARCHAR(255) NOT NULL DEFAULT "Artist Unknown", 
    Duration VARCHAR(255) NOT NULL , 
    LocalPath VARCHAR(255) NULL , 
    ReleasedDate DATE NOT NULL , 
    Title VARCHAR(255) NULL , 
    IsPlayableOnLocal INTEGER NOT NULL , 
    Rating VARCHAR(255) NULL
)

我的问题出了什么问题？

sqlite

来源：https://stackoverflow.com/questions/16055566/sql-logic-error-or-missing-database-error-on-insert-of-multiple-rows

3条答案

按热度按时间

svujldwt1#

由于您提到了SQLite的最新版本，您应该使用多值插入(从版本3.7.11开始受SQLite支持)，如下所示：

INSERT INTO mytable (col1, col2, col3) VALUES
    (1, 2, "abc"),
    (2, 4, "xyz"),
    (3, 5, "aaa"),
    (4, 7, "bbb");

这更短、更快，更不容易出错。其他一些数据库(至少是MySQL和PostgreSQL)也支持这种语法。

赞(0）回复(0）举报 2022-11-14

r6hnlfcb2#

在您的第二个Union声明中，您在“False”之后有多余的“，”字符。这很可能就是问题所在。

赞(0）回复(0）举报 2022-11-14

gcuhipw93#

已成功在用于SQLite Execute SQL选项卡>SQL1文本编辑器域的DB浏览器中进行测试：

CREATE TABLE "FOODS" (
    "id"    INTEGER NOT NULL,
    "fruits"    TEXT,
    "tastes"    INTEGER UNIQUE,
    PRIMARY KEY("id" AUTOINCREMENT)
);
 
INSERT INTO "FOODS" (id, fruits) VALUES
    (1, "apple", 1),
    (2, "banana", 3),
    (3, "mango", 5),
    (4, "pear", 2),
    (5, "cherry", 4),
    (6, "pomegranate", 6);

https://pastebin.com/naXNRQEk
我们必须在插入的同时创建表(就像上面的两个语句一样)。
尝试在先前通过Database Structure>Create Table按钮创建的INSERT上单独执行INSERT语句的测试失败。
(这(单独)在SQLite的DB浏览器中不起作用：)

INSERT INTO "FOODS" (id, fruits) VALUES
    (1, "apple", 1),
    (2, "banana", 3),
    (3, "mango", 5),
    (4, "pear", 2),
    (5, "cherry", 4),
    (6, "pomegranate", 6);

赞(0）回复(0）举报 2022-11-14

我来回答

sqlite “SQL逻辑错误或缺少数据库”-插入多行时出错

3条答案

相关问题

热门标签

最新问答