I am trying to update a a row using a SQL task. The value I want to set it to is based on the count of another table. I am able to select the exact row by using the parameter I have stored previously. The problem is that SQL Server won't allow a parameter value when a subquery is being used.
The update statement:
SET WORKFORCE02WEMPLOYEE = (SELECT COUNT(1)
WHERE execution_date = ?
Why is this so!! I can get around it by adding another parameter to store the result of the count and populate it as a separate step, however I have 20+ table counts to store - it seems a messy way round the problem.
Any other options??