SQL Server Update a Table with a Column Count of another Table

idv4meu8  于 2023-08-02  发布在  其他
关注(0)|答案(3)|浏览(112)

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.

xytpbqjk

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 the SalesForm in the subquery since it's correlated with the outer reference.

UPDATE dbo.SalesForm
SET Pictures = (SELECT COUNT(*)
                FROM dbo.storeImages b
                 WHERE SalesForm.Id = b.WorkOrderId
                );
jbose2ul

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.

UPDATE main
SET Pictures = (SELECT COUNT(b.WorkOrderId) FROM dbo.storeImages b WHERE main.Id = b.WorkOrderId)
FROM dbo.SalesForm AS main
kxkpmulp

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.

CREATE VIEW dbo.StoreImagesById
WITH SCHEMABINDING     -- must be schema-bound
SELECT
  si.WorkOrderId,
  COUNT_BIG(*) AS CountOfImages    -- must use COUNT_BIG not COUNT
FROM dbo.StoreImages si
GROUP BY
  si.WorkOrderId;
CREATE UNIQUE CLUSTERED INDEX pk_WorkOrderId ON dbo.StoreImagesById (WorkOrderId);

相关问题