I am getting an error when I create this Query to update a Table with another Table's column count.
update [dbo].[SalesForm]
SET Pictures = (SELECT COUNT(b.WorkOrderId) as a
FROM
dbo.SalesForm a
INNER JOIN dbo.storeImages b
ON a.Id = b.WorkOrderId
group by b.WorkOrderId)
This query generated this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The Sales Table Query, on its own generated these results:
1
1
2
1
1
I would like to update the Pictures column with the results count values from the Pictures Table as a report of how many photos exist in each record.
3条答案
按热度按时间xytpbqjk1#
Add correlation criteria to the subquery so that a scalar value is returned for each
SalesForm
row with the corresponding count. You don't need to repeat theSalesForm
in the subquery since it's correlated with the outer reference.jbose2ul2#
Your subquery always returns a list of numbers, because you have no condition which matches the entry of SalesForm to the corresponding counted number in your Subquery. Therefore, I added a where clause which matches the entry to update with the entry in the subquery.
kxkpmulp3#
You shouldn't store the count of an existing table, you would need to constantly keep it in sync using triggers or similar.
Instead just use an indexed view. The server will keep it in sync.