在INSERT语句中使用SQL子查询

Here are the two tables created:

以下是创建的两个表:

CREATE TABLE category_tbl(
id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
subcategory varchar(255) NOT NULL,
PRIMARY KEY(id),
CONSTRAINT nameSubcategory UNIQUE KEY(name, subcategory)
) ENGINE=InnoDB;

CREATE TABLE device(
id INT NOT NULL AUTO_INCREMENT,
cid INT DEFAULT NULL,
name VARCHAR(255) NOT NULL,
received DATE,
isbroken BOOLEAN,
PRIMARY KEY(id),
FOREIGN KEY(cid) REFERENCES category_tbl(id)
) ENGINE=InnoDB;

Below is the instruction that was given to me:

以下是给我的指示:

-- insert the following devices instances into the device table (you should use a subquery to set up foriegn keys referecnes, no hard coded numbers):
-- cid - reference to name: phone subcategory: maybe a tablet?
-- name - Samsung Atlas
-- received - 1/2/1970
-- isbroken - True

I'm getting errors on the insert statement below from attempting to use a sub-query within an insert statement. How would you solve this issue?

我在下面的插入语句中尝试在insert语句中使用子查询时遇到错误。你会如何解决这个问题?

INSERT INTO devices(cid, name, received, isbroken)
VALUES((SELECT id FROM category_tbl WHERE subcategory = 'tablet')  , 'Samsung Atlas', 1/2/1970, 'True');

2 个解决方案

#1

~~It's not possible to use a SELECT in an INSERT ... VALUES ... statement. The key here is the VALUES keyword.~~ (EDIT: It is actually possible, my bad.)

在INSERT ... VALUES ...语句中不可能使用SELECT。这里的关键是VALUES关键字。 (编辑:实际上可能,我很糟糕。)

If you remove the VALUES keyword, you can use the INSERT ... SELECT ... form of the INSERT statement statement.

如果删除VALUES关键字,则可以使用INSERT语句语句的INSERT ... SELECT ...形式。

For example:

INSERT INTO mytable ( a, b, c) SELECT 'a','b','c'

In your case, you could run a query that returns the needed value of the foreign key column, e.g.

在您的情况下,您可以运行一个返回所需外键列值的查询,例如

SELECT c.id
  FROM category_tbl c
 WHERE c.name = 'tablet'
 ORDER BY c.id
 LIMIT 1

If we add some literals in the SELECT list, like this...

如果我们在SELECT列表中添加一些文字,就像这样......

SELECT c.id              AS `cid`
     , 'Samsung Atlas'   AS `name`
     , '1970-01-02'      AS `received`
     , 'True'            AS `isBroken`
  FROM category_tbl c
 WHERE c.name = 'tablet'
 ORDER BY c.id
 LIMIT 1

That will return a "row" that we could insert. Just precede the SELECT with

这将返回我们可以插入的“行”。就在SELECT之前

INSERT INTO device (`cid`, `name`, `received`, `isbroken`)

NOTE: The expressions returned by the SELECT are "lined up" with the columns in the column list by position, not by name. The aliases assigned to the expressions in the SELECT list are arbitrary, they are basically ignored. They could be omitted, but I think having the aliases assigned makes it easier to understand when we run just the SELECT portion.

注意:SELECT返回的表达式与列列表中的列按位置“排列”,而不是按名称排列。分配给SELECT列表中表达式的别名是任意的,它们基本上被忽略。它们可以省略,但我认为分配别名会使我们在运行SELECT部分时更容易理解。

#2

You have different table name in CREATE TABLE and INSERT INTO so just choose one device or devices

您在CREATE TABLE和INSERT INTO中有不同的表名,因此只需选择一个或多个设备即可
When insert date format use the good one like DATE('1970-02-01')

插入日期格式使用像DATE('1970-02-01')这样的好日期格式
When insert boolean - just TRUE with no qoutes I beleive.

当插入布尔值时 - 只有TRUE没有qoutes我相信。

http://sqlfiddle.com/#!9/b7180/1

INSERT INTO devices(cid, name, received, isbroken)
VALUES((SELECT id FROM category_tbl WHERE subcategory = 'tablet')  , 'Samsung Atlas', DATE('1970-02-01'), TRUE);

#1