Scorecard table:

| lsp | werks | Success |
| --- | --- | --- |
| AAA | ZZZ |  |
| BBB | YYY |  |
| CCC | XXX |  |

Inventory table:

| lsp | werks | Status |
| --- | --- | --- |
| AAA | ZZZ | SUCCESS |
| AAA | ZZZ | SUCCESS |
| AAA | ZZZ | SUCCESS |
| AAA | ZZZ | SUCCESS |
| BBB | YYY | SUCCESS |
| BBB | YYY | SUCCESS |
| BBB | YYY | SUCCESS |
| BBB | YYY | FAILURE |

I want to write a query to update the `Scorecard` table with count of success from the `Inventory` table on a defined lsp

This is my query in SQL Server:

```
UPDATE [Scorecard] 
SET Success = (SELECT COUNT([INVENTORY].[Status]) 
               FROM [INVENTORY]  
               WHERE [INVENTORY].STATUS = SUCCESS 
                 AND [INVENTORY].[werks] = [Scorecard].[werks])

```