Shilpa Nagavara Shilpa Nagavara -3 years ago 48
SQL Question

Update count of union

I have this query which is throwing a compilation error at the last ')'. The intellisense says 'Expected AS, ID or QUOTED_ID'.

What I am trying to do is - find the distinct values from the union of a table select and a function select, then get the count and update the column of another table with that value.

UPDATE #referees
SET [TotalKeywordCount] = (select count(*)
from (select Keyword
from [dbo].[RefereeFinderPersonKeyWord] P
where P.p_id=#referees.p_id

union

SELECT ltrim(rtrim(replace(Data, '''', '')))
from [SplitOne] (@keywords, ',')))


Any idea what I am doing wrong?

Answer Source

You need to add a name to the nested query that you use in the FROM of the query that pulls out the value for [TotalKeywordCount]. Below you have the code that assigns to it the name subquery:

UPDATE #referees
SET [TotalKeywordCount] = (select count(*) from (
        select Keyword from [dbo].[RefereeFinderPersonKeyWord] P where P.p_id=#referees.p_id
        union 
        SELECT ltrim(rtrim(replace(Data, '''', ''))) from [SplitOne] (@keywords, ',')) subquery ) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download