Update task using parameter and subquery

Last Post 13 Mar 2006 03:30 PM by superboot. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
superboot
New Member
New Member

--
13 Mar 2006 02:38 PM
Hi,

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:

UPDATE TBL_REFRESH_HISTORY
SET WORKFORCE02WEMPLOYEE = (SELECT COUNT(1)
FROM WORKFORCE02WEMPLOYEE)
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??
Cheers
Darcy Wright
superboot
New Member
New Member

--
13 Mar 2006 03:30 PM
I have been able to get around this problem by changing the statement to:

UPDATE TBL_REFRESH_HISTORY
SET WORKFORCE02WEMPLOYEE = (SELECT COUNT(1)
FROM WORKFORCE02WEMPLOYEE)
WHERE execution_date = (SELECT MAX(execution_date)
FROM TBL_REFRESH_HISTORY)

Cheers
You are not authorized to post a reply.

Acceptable Use Policy