嵌套插入和选择语句 [英] Nested Insert into and select statement

问题描述

我的 MySql 数据库中有以下结构:

I have following structur in my MySql database:

1 个表称为报告，1 个表称为产品

1 Table called reports and 1 Table called product

我现在想根据选择的产品插入报告.我得到的错误如下:

I want now to insert into reports based on a selection of products. The error i get is following:

错误代码:1242.子查询返回超过 1 行 0.000 秒

Error Code: 1242. Subquery returns more than 1 row 0.000 sec

在我的最后一个选择语句(只选择，没有插入)中，我用关键字IN"解决了这个错误，但在这种情况下它不起作用.这是我到目前为止的查询(产生错误)

In my last select statement (only select, no insert) i solved this error with the Keyword "IN" but in this case it dont work. Here is the query i have so far (which produces the error)

INSERT INTO reports (report_date, report_emploee, report_content, report_art, report_adressnummer)
VALUES(
NOW(), 
'UpdateMaster', 
'content', 
'AutoUpdate' , 
(SELECT product.product_adressnummer 
FROM product 
WHERE product.product_name='testproduct'
AND product.product_version='2.50c' 
AND product_updateDatum >= '2015-12-11'));

我尝试用我的 select 语句创建一个数组，然后在插入报告期间对其进行迭代，但我没有在 sql 中得到它.网上所有资料都是结合sql和php来实现的.

I tried to create an array with my select statement and then iterate over it during insert into reports, but i dont get it in sql. All informations online combine sql and php to get it work.

如果我要执行查询，它将如下所示:

If i would execute the query it would look like this:

report_date=today
report_emploee='UpdateMaster'
report_content='content'
report_art='AutoUpdate'
report_adressnummer=123,456,789,310,...

但它应该像这样执行:

report_date=today
report_emploee='UpdateMaster'
report_content='content'
report_art='AutoUpdate'
report_adressnummer=123

report_date=today
report_emploee='UpdateMaster'
report_content='content'
report_art='AutoUpdate'
report_adressnummer=456

report_date=today
report_emploee='UpdateMaster'
report_content='content'
report_art='AutoUpdate'
report_adressnummer=789

.......

您的解决方案影响了 sql 表中的 0 行.

You solution affected 0 rows in sql table.

如果我执行这个查询:

SELECT contact.contact_vorname, contact.contact_nachname, contact.contact_eMail
FROM contact 
WHERE contact.contact_adressnummer IN
(SELECT product.product_adressnummer 
FROM product 
WHERE product.product_name='toolstar®TestLX'
AND product.product_version='2.50c' 
AND product_updateDatum >= '2015-12-11');

它返回 8 行，您的解决方案也应该影响 8 行，对吗?

it returns 8 rows and your solution should also affect 8 rows, right?

推荐答案

您遇到的问题是当您尝试插入

The problem you have is when you try and insert the result of

SELECT product.product_adressnummer 
FROM product 
WHERE product.product_name='testproduct'
AND product.product_version='2.50c' 
AND product_updateDatum >= '2015-12-11'

进入你的桌子.由于这会返回多条记录，因此您无法将其插入到应有一条记录的位置.IN 不能解决问题，因为这不会阻止返回多条记录.

into your table. As this returns more than one record, you cannot insert it where one record should be. IN does not solve the problem as this does not prevent more than one record being returned.

如果您想为返回的每条记录插入一条记录，您可以使用:

If you want to insert a record for every record returned you could use:

INSERT INTO 
    reports (report_date, report_emploee, report_content, report_art, report_adressnummer)
SELECT
    NOW(), 
    'UpdateMaster', 
    'content', 
    'AutoUpdate' ,
    product.product_adressnummer 
FROM product 
WHERE product.product_name='testproduct'
AND product.product_version='2.50c' 
AND product_updateDatum >= '2015-12-11'

这篇关于嵌套插入和选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！