UPDATE from Sub-select problem

[HeavenCore]

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:

Update EnterpriseBuffer SET EnterpriseBuffer.AnalystDepartment = 
(SELECT dbo.EnterpriseProdGroupToAnalystDeptMap.AnalystDepartment 
FROM EnterpriseBuffer LEFT OUTER JOIN dbo.EnterpriseProdGroupToAnalystDeptMap ON dbo.[EnterpriseBuffer ].PG = dbo.EnterpriseProdGroupToAnalystDeptMap.EnterprisePG)
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.

[deepuv04]

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:

Update EnterpriseBuffer SET EnterpriseBuffer.AnalystDepartment = 
(SELECT dbo.EnterpriseProdGroupToAnalystDeptMap.AnalystDepartment 
FROM EnterpriseBuffer LEFT OUTER JOIN dbo.EnterpriseProdGroupToAnalystDeptMap ON dbo.[EnterpriseBuffer ].PG = dbo.EnterpriseProdGroupToAnalystDeptMap.EnterprisePG)
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)

[HeavenCore]

Spot on!

Thanks alot, really appreciate that :D