子查询返回了多行

Question

我正在编写一个向表中插入值的查询语句。

但是它给了我一个错误提示。

我的查询语句如下：

INSERT INTO res_partner(
                        name,
                        company_id,
                        create_date,
                        street,
                        city,
                        display_name,
                        zip,
                        supplier,
                        ref,
                        is_company,
                        customer,
                        street2,
                        employee,
                        write_date,
                        active,
                        write_uid,
                        lang,
                        create_uid,
                        notify_email) 
       VALUES(
              (SELECT shipping_address_name FROM temp_unicom),
              1,
              LOCALTIMESTAMP,
              (SELECT shipping_address_line_1 FROM temp_unicom;),
              (SELECT shipping_address_city FROM temp_unicom),
              (SELECT shipping_address_name FROM  temp_unicom),
              (SELECT shipping_address_pincode FROM temp_unicom),
              FALSE,
              (Select sale_order_item_code FROM temp_unicom),
              FALSE,
              TRUE,
              (SELECT shipping_address_line_2 FROM temp_unicom),
              FALSE,
              LOCALTIMESTAMP,
              TRUE,
              1,
              'en_US',
              1,
              'always');

错误：

错误：子查询作为表达式返回了多行

********** 错误 **********

ERROR: more than one row returned by a subquery used as an expression
SQL state: 21000

我知道每个选择子查询都会返回多行结果，但我不知道如何修复错误。 我的temp_unicom表中大约有15,000行数据，我正在尝试将temp_unicom的数据插入到res_partner中。

- Manish Gupta

---

你的子查询需要加上限定条件以返回单一值。在“insert into”语句中无法插入多个值。 - artm

问题是我没有where子句。我需要从temp_unicom复制数据到res_partner。LOCALTIMESTAMP提供了我的系统的当前时间戳。 - Manish Gupta

insert into res_partner (col1,col2,col3,col4,col5) select cola,colb,1,'en_US',colc from temp_unicom - Vivek S.

Answer 1

您的代码不符合insert ... select语句的正确语法。如果您想从temp_unicom表中插入多行数据，您应该按照以下方式重写查询：

Insert into res_partner
(name,company_id,create_date,street,city,display_name,zip,supplier,ref,is_company,customer,street2,employee,write_date,active,write_uid,lang,create_uid,notify_email) 
Select shipping_address_name,1, LOCALTIMESTAMP, shipping_address_name, 
shipping_address_line_1,shipping_address_city,
shipping_address_name,shipping_address_pincode,sale_order_item_code,FALSE,
shipping_address_line_2,FALSE, LOCALTIMESTAMP, TRUE,1,'en_US',1,'always'
from temp_unicom

- Robert