CODEWITHSUNDEEP
sqlsql-server
Puneeth
Puneeth

Reputation: 33

Subquery returned more than 1 value. SQL server

i am getting following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

below is my query i just want to add PrePmt_Amount from table sales header with Amount column from sales line and store the result in column testing in Table sales header note- sales header.No_ is the primary key similarly for sales line.[Document No_]

Query=>

update sales_header 
set testing  =
    (select sales_header.PrePmt_Amount + sales_line.amount 
    from sales_header,sales_line 
    where sales_header.No_ = sales_line.[Document No_])

Upvotes: 1

Views: 66

Answers (2)

Edward
Edward

Reputation: 762

Your SELECT statement is returning more than one record, that means that there is more than one record in sales_header, sales_line that matches your WHERE clause sales_header.No_ = sales_line.[Document No_]

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think you intend:

update sales_header
    set testing = (PrePmt_Amount +
                   (select sum(sl.amount)
                    from sales_line
                    where sales_header.No_ = sl.[Document No_]
                   )
                  );

Upvotes: 1

Related Questions