Update Table A based upon secondary query of Table A & Table B

[mrobinsc]

** This SQL statement returns 4 rows

SELECT
COUNT(*)
G.ACTIVITY_ID
G.RESOURCE_TYPE
G.RESOURCE_CATE GORY
G.RESOURCE_SUB_ CAT
G.ANALYSIS_TYPE
G.PROJECT_ID
FROM PS_SOI_COAMAP F with (nolock)
, PS_SOI_CNV_TMP0 60 G with (nolock)
WHERE F.BUSINESS_UNIT = '00300'
AND F.BUSINESS_UNIT = G.BUSINESS_UNIT
AND F.ACTIVITY_ID = G.ACTIVITY_ID
AND F.RESOURCE_TYPE = G.RESOURCE_TYPE
AND F.RESOURCE_CATE GORY = G.RESOURCE_CATE GORY
AND F.RESOURCE_SUB_ CAT = G.RESOURCE_SUB_ CAT
AND F.ANALYSIS_TYPE = G.ANALYSIS_TYPE
AND F.PROJECT_ID = G.PROJECT_ID
AND F.PROJECT_ID = '300FW0WILD0750 3'
GROUP BY G.ACTIVITY_ID
,G.RESOURCE_TYP E
,G.RESOURCE_CAT EGORY
,G.RESOURCE_SUB _CAT
,G.ANALYSIS_TYP E
,G.PROJECT_ID
HAVING COUNT(*) > 1

** Now I want to Update table G where the criteria in the previous query is met. So I attempt to use WHERE EXIST, but I update all rows in table G instead of the 4 rows from the previous query. Can anyone tell me what I am doing wrong? Thanks in advance.

UPDATE PS_SOI_CNV_TMP0 60
SET SOI_FLAG = 'D'
WHERE EXISTS (
SELECT
COUNT(*)
, G.ACTIVITY_ID
, G.RESOURCE_TYPE
, G.RESOURCE_CATE GORY
, G.RESOURCE_SUB_ CAT
, G.ANALYSIS_TYPE
, G.PROJECT_ID
FROM PS_SOI_COAMAP F with (nolock)
, PS_SOI_CNV_TMP0 60 G with (nolock)
WHERE F.BUSINESS_UNIT = '00300'
AND F.BUSINESS_UNIT = G.BUSINESS_UNIT
AND F.ACTIVITY_ID = G.ACTIVITY_ID
AND F.RESOURCE_TYPE = G.RESOURCE_TYPE
AND F.RESOURCE_CATE GORY = G.RESOURCE_CATE GORY
AND F.RESOURCE_SUB_ CAT = G.RESOURCE_SUB_ CAT
AND F.ANALYSIS_TYPE = G.ANALYSIS_TYPE
AND F.PROJECT_ID = G.PROJECT_ID
AND F.PROJECT_ID = '300FW0WILD0750 3'
GROUP BY G.ACTIVITY_ID
,G.RESOURCE_TYP E
,G.RESOURCE_CAT EGORY
,G.RESOURCE_SUB _CAT
,G.ANALYSIS_TYP E
,G.PROJECT_ID
HAVING COUNT(*) > 1 )

[Delerna]

according to the SQL servers help documentation
here is the code to update a table from another table

UPDATE titles
    SET t.ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
 
 

so to update from a query, write your query
and do something like this.

UPDATE titles
    SET t.ytd_sales = t.ytd_sales + s.qty
    FROM titles t, myQueryName s
    WHERE t.title_id = s.title_id
 

If you don't want to have a separate query then wrap the query up in brackets
and include it directly in the update query.

UPDATE titles
    SET t.ytd_sales = t.ytd_sales + s.qty
    FROM titles t, (SELECT Title_ID,Qty FROM Sales) s
    WHERE t.title_id = s.title_id