CODEWITHSUNDEEP
sql
Suchit G
Suchit G

Reputation: 23

Executing subquery to insert data on another table

I am getting this error while executing below query:

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

This is the query I am using:

UPDATE BatchRecords SET Fees = (
    SELECT 
        (Fees/NoOfPayments) as AvgTotal
    FROM BatchFx
    WHERE BatchRecords.BatchId = BatchFx.BatchId
    AND BatchFx.CreatedOn < '2019-05-04'
);

Upvotes: 1

Views: 35

Answers (2)

Michele La Ferla
Michele La Ferla

Reputation: 6884

Your error is happening because your WHERE clause is returning more than 1 record. So the update statement is confused which row does it need to update (because you most probably have more than 1 record per ID in the BatchFX table). I replicated your issue in this link and got the same output.

So to solve this issue you need to use an aggregator to group all the rows and output one record from the subquery.

UPDATE BatchRecords
SET Fees = (
    SELECT
      AVG(Fees / NoOfPayments) as AvgTotal
    FROM BatchFx
    WHERE BatchRecords.BatchId = BatchFx.BatchId
    AND BatchFx.CreatedOn < '2019-05-04'
);

Hope this helps :)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

The alias AvgTotal seems to imply that you want to take an average of something, so why not try doing that:

UPDATE BatchRecords br
SET Fees = (SELECT AVG(Fees/NoOfPayments) AS AvgTotal
            FROM BatchFx bf
            WHERE br.BatchId = bf.BatchId AND bf.CreatedOn < '2019-05-04');

Note that the error message you are seeing implies that the subquery in some cases would return more than one record. Selecting an aggregate function would be one way to get around this problem.

Upvotes: 1

Related Questions