CODEWITHSUNDEEP
sqlpostgresqlsubquerysql-insert
Manish Gupta
Manish Gupta

Reputation: 4656

more than one row returned by a subquery

I am writing a query to insert values in a table. It is giving me an error.

My query is:

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:

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

********** Error **********

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

I know that each select subquery returns multiple rows, but I don't know how to fix the error. I have approximately 15,000 rows in my temp_unicom table and I am trying to insert data from temp_unicom to res_partner.

Upvotes: 4

Views: 4159

Answers (1)

Robert
Robert

Reputation: 25753

Your code is not correct syntax for insert ... select statement. If you want to insert many rows from temp_unicom table you should rewrite your query as below

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

Upvotes: 9

Related Questions