By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

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

P: 1
** This SQL statement returns 4 rows

SELECT
COUNT(*)
G.ACTIVITY_ID
G.RESOURCE_TYPE
G.RESOURCE_CATEGORY
G.RESOURCE_SUB_CAT
G.ANALYSIS_TYPE
G.PROJECT_ID
FROM PS_SOI_COAMAP F with (nolock)
, PS_SOI_CNV_TMP060 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_CATEGORY = G.RESOURCE_CATEGORY
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 = '300FW0WILD07503'
GROUP BY G.ACTIVITY_ID
,G.RESOURCE_TYPE
,G.RESOURCE_CATEGORY
,G.RESOURCE_SUB_CAT
,G.ANALYSIS_TYPE
,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_TMP060
SET SOI_FLAG = 'D'
WHERE EXISTS (
SELECT
COUNT(*)
, G.ACTIVITY_ID
, G.RESOURCE_TYPE
, G.RESOURCE_CATEGORY
, G.RESOURCE_SUB_CAT
, G.ANALYSIS_TYPE
, G.PROJECT_ID
FROM PS_SOI_COAMAP F with (nolock)
, PS_SOI_CNV_TMP060 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_CATEGORY = G.RESOURCE_CATEGORY
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 = '300FW0WILD07503'
GROUP BY G.ACTIVITY_ID
,G.RESOURCE_TYPE
,G.RESOURCE_CATEGORY
,G.RESOURCE_SUB_CAT
,G.ANALYSIS_TYPE
,G.PROJECT_ID
HAVING COUNT(*) > 1 )
Feb 12 '08 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
according to the SQL servers help documentation
here is the code to update a table from another table
Expand|Select|Wrap|Line Numbers
  1. UPDATE titles
  2.     SET t.ytd_sales = t.ytd_sales + s.qty
  3.     FROM titles t, sales s
  4.     WHERE t.title_id = s.title_id
  5.  
  6.  
so to update from a query, write your query
and do something like this.

Expand|Select|Wrap|Line Numbers
  1. UPDATE titles
  2.     SET t.ytd_sales = t.ytd_sales + s.qty
  3.     FROM titles t, myQueryName s
  4.     WHERE t.title_id = s.title_id
  5.  
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.

Expand|Select|Wrap|Line Numbers
  1. UPDATE titles
  2.     SET t.ytd_sales = t.ytd_sales + s.qty
  3.     FROM titles t, (SELECT Title_ID,Qty FROM Sales) s
  4.     WHERE t.title_id = s.title_id
  5.  
Feb 12 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.