CODEWITHSUNDEEP
sqlsql-server
George Francis
George Francis

Reputation: 502

UPDATE a Column with a SELECT statement

I have a table FactSales with the following columns:

Revenue, Quantity, UnitCost

This is how the table looks like:

UnitCost   | Revenue | Quantity
-----------+---------+----------
NULL       | 50.0    | 5
NULL       | 70.0    | 3

Now I want to update the data in UnitCost to be equal to Revenue / Quantity, how ever when using the following query

UPDATE FactSales 
SET UnitCost = (SELECT Revenue / Quantity
                FROM FactSales);

I get the 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.

How should the update statement be written as to successfully update the values?

Upvotes: 2

Views: 1233

Answers (2)

sticky bit
sticky bit

Reputation: 37487

Don't use a subquery, just an expression is enough.

UPDATE FactSales 
       SET UnitCost = Revenue / Quantity;

Your subquery retrieves more than one row, if the table has more than one row. But in the given context only subqueries returning not more that one row are allowed. But in your query a subquery isn't needed at all.

Upvotes: 10

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

your query analysis 

UPDATE FactSales 
SET UnitCost = (
SELECT Revenue / Quantity
FROM FactSales
);

here in this subquery returns multiple values, SQL is complaining because it can't assign multiple values to UnitCost 

(
    SELECT Revenue / Quantity
    FROM FactSales
    );


    output 
 Revenue / Quantity
    10 --1st row
    23.333333333333332 --2nd row

as a result it throws that error

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

So void using sub-query just do it in single query like below

UPDATE FactSales 
       SET UnitCost = Revenue / Quantity; --here for each row single value will return

Note:This is not permitted when the

subquery follows =, !=, <, <= , >, >= means for this type of operators not allowed multiple values in sql engine .

Upvotes: 1

Related Questions