CODEWITHSUNDEEP
sqlt-sql
General Esdeath
General Esdeath

Reputation: 21

The subquery returned more than one value. TSQL

CREATE TABLE sales
(id INT PRIMARY KEY IDENTITY, name VARCHAR(30),
percent_part FLOAT, sales FLOAT, sum_bonus DECIMAL);

CREATE TRIGGER TRcointingBonus ON sales 
AFTER UPDATE 
AS BEGIN

DECLARE @sum_bonus FLOAT;
SELECT @sum_bonus = (SELECT ((sales / 100) * percent_part) FROM  sales);
UPDATE sales SET sum_bonus = @sum_bonus

END;

INSERT INTO sales VALUES('staff1', 7.0, 7088, 1);
INSERT INTO sales VALUES('staff2', 3.5, 20590, 1);
INSERT INTO sales VALUES('staff3', 10.5, 6089, 1);

UPDATE sales SET sales = 7088 WHERE id=1;

I create a table and a trigger, and for each UPATE operation, there will be calculations in each row of sum_bonus.

The problem is in

DECLARE @sum_bonus FLOAT;
SELECT @sum_bonus = (SELECT ((sales / 100) * percent_part) FROM sales);

If remove FROM sales then writing to the variable will not be possible. Can explain what is the reason and how to solve this problem without abandoning the variable?

Upvotes: 1

Views: 58

Answers (1)

GarethD
GarethD

Reputation: 69749

Your actual error is very clear, your subquery returns more than one value. If you take your sample data, and run your subquery on it's own:

SELECT ((sales / 100) * percent_part) 
FROM  sales

You get 3 values:

(No column name)
496.16
720.65
639.345

You are then trying to assign this to a single decimal variable:

DECLARE @sum_bonus FLOAT;
SELECT @sum_bonus = (SELECT ((sales / 100) * percent_part) FROM sales);

So SQL Server has no idea which of the 3 values you are expecting to be stored, so throws an error - clearly telling you that the subquery can only return one row to avoid ambiguity.

To avoid the error change your subquery to return one row.

With that being said your trigger is massively flawed, it updates the entire table every time as it has no reference to either the inserted or deleted memory resident pseudo tables, and as above the value it is trying to update the entire table with doesn't really make sense either.

What you are trying to achieve can be done very simply with a Computed Column, so your create table becomes something like:

CREATE TABLE sales
(
    id INT PRIMARY KEY IDENTITY, 
    name VARCHAR(30),
    percent_part FLOAT, 
    sales FLOAT, 
    sum_bonus AS (sales * percent_part / 100)
);

There's no need to store sum_bonus and use any kind of code to update it when underlying values change, just store the expression and calculate it as and when you need it.

Example on db<>fiddle

ADDENDUM

To answer a question you have asked in the comments as to why this works:

UPDATE sales SET sum_bonus = (SELECT (sales / 100) * procent)

But adding FROM sales breaks the query - It is because adding FROM Sales fundamentally changes the query from just superfluous use of parenthesis and SELECT, to a subquery. The former is equivalent to simply:

UPDATE sales SET sum_bonus = sales / 100 * percent

i.e. sales and percent are references to columns from the instance of the sales being updated. By adding FROM sales you introduce a further instance of the sales table, and the two columns then become references to that. With no link to the instance being updated you end up with multiple rows in the subquery, and hence your error.

Upvotes: 2

Related Questions