CODEWITHSUNDEEP
sql-server
La Van Hai
La Van Hai

Reputation: 19

Subquery returned more than 1 value. SQL error

This is error that i has it. So i dont know to do. Please help me.

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

UPDATE Kho
/*SET Kho.Xuat = SUM(PhieuXuat.SL)*/
SET Kho.Xuat = (SELECT SUM(PhieuXuat.SL) AS SLuong FROM PhieuXuat group by PhieuXuat.MaVT)
FROM Kho
INNER JOIN PhieuXuat
ON (Kho.MaVT = PhieuXuat.MaVT)

Upvotes: 0

Views: 51

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

The error is because your inner query is returning more than one row. You can try to sum on group inside the JOIN and then join on this set to get correct results.

NB: The inner query will be evaluated only once.

UPDATE Kho
/*SET Kho.Xuat = SUM(PhieuXuat.SL)*/
SET Kho.Xuat = sumSL
FROM Kho
INNER JOIN 
(SELECT SUM(PhieuXuat.SL) as sumSL,PhieuXuat.MaVT FROM PhieuXuat group by PhieuXuat.MaVT )P
ON (Kho.MaVT = P.MaVT)

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

This means that the subquery returned more than one row which is not allowed. You can write your UPDATE statement as:

UPDATE k 
    SET k.Xuat = (SELECT SUM(p.SL) FROM PhieuXuat p WHERE p.MaVT = k.MaVT)
FROM Kho k

Note the added WHERE clause inside the subquery. I also removed the JOIN to prevent multiple updates on the same row.

Upvotes: 2

Related Questions