469,613 Members | 1,888 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

UPDATE from Sub-select problem

Hello all,

i have a process which reads 2 csv files into two different tables (thus there is no real primary key etc)

i need to update 1 column in one table (EnterpriseBuffer) with data from another table (EnterpriseProdGroupToAnalystDeptMap.AnalystDepart ment). This is done where EnterpriseBuffer.PG = EnterpriseProdGroupToAnalystDeptMap.EnterprisePG.

I attempted the following:

Expand|Select|Wrap|Line Numbers
  1. Update EnterpriseBuffer SET EnterpriseBuffer.AnalystDepartment = 
  2. (SELECT dbo.EnterpriseProdGroupToAnalystDeptMap.AnalystDepartment 
  3. FROM EnterpriseBuffer LEFT OUTER JOIN dbo.EnterpriseProdGroupToAnalystDeptMap ON dbo.[EnterpriseBuffer ].PG = dbo.EnterpriseProdGroupToAnalystDeptMap.EnterprisePG)
  4. WHERE EnterpriseBuffer.PIPCode NOT IN (select buyingcode from AnalystBuffer)
However this fails due to:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
now i understand why this occurs, as the sub select returns 10700 rows, the same as a select * from EnterpriseBuffer would return. i just need to update analystdepartment column for each row in EnterpriseBuffer with the EnterpriseBuffer version in EnterpriseProdGroupToAnalystDeptMap.

This sounds very coimplicating i know, but please, any information or resolution to this would be helpful.
Nov 21 '07 #1
2 1380
deepuv04
227 Expert 100+
Hello all,

i have a process which reads 2 csv files into two different tables (thus there is no real primary key etc)

i need to update 1 column in one table (EnterpriseBuffer) with data from another table (EnterpriseProdGroupToAnalystDeptMap.AnalystDepart ment). This is done where EnterpriseBuffer.PG = EnterpriseProdGroupToAnalystDeptMap.EnterprisePG.

I attempted the following:

Expand|Select|Wrap|Line Numbers
  1. Update EnterpriseBuffer SET EnterpriseBuffer.AnalystDepartment = 
  2. (SELECT dbo.EnterpriseProdGroupToAnalystDeptMap.AnalystDepartment 
  3. FROM EnterpriseBuffer LEFT OUTER JOIN dbo.EnterpriseProdGroupToAnalystDeptMap ON dbo.[EnterpriseBuffer ].PG = dbo.EnterpriseProdGroupToAnalystDeptMap.EnterprisePG)
  4. WHERE EnterpriseBuffer.PIPCode NOT IN (select buyingcode from AnalystBuffer)
However this fails due to:



now i understand why this occurs, as the sub select returns 10700 rows, the same as a select * from EnterpriseBuffer would return. i just need to update analystdepartment column for each row in EnterpriseBuffer with the EnterpriseBuffer version in EnterpriseProdGroupToAnalystDeptMap.

This sounds very coimplicating i know, but please, any information or resolution to this would be helpful.

Try the following query......

Update EnterpriseBuffer SET EnterpriseBuffer.AnalystDepartment =
(SELECT dbo.EnterpriseProdGroupToAnalystDeptMap.AnalystDep artment
FROM dbo.EnterpriseProdGroupToAnalystDeptMap
WHERE EnterprisePG = dbo.[EnterpriseBuffer].PG)

WHERE EnterpriseBuffer.PIPCode NOT IN (select buyingcode from AnalystBuffer)
Nov 23 '07 #2
Spot on!

Thanks alot, really appreciate that :D
Nov 23 '07 #3

Post your reply

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

Similar topics

1 post views Thread by MrMike | last post: by
3 posts views Thread by Terry Olsen | last post: by
9 posts views Thread by Jon | last post: by
4 posts views Thread by irfi | last post: by
3 posts views Thread by Henry Stockbridge | last post: by
1 post views Thread by Sharon | last post: by
1 post views Thread by =?Utf-8?B?UmljaA==?= | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.