June 29, 2011 at 2:43 am
Hi All,
I am trying to update one of my column using update statement but when i executed my query i got following error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
My query is :
update target_segment
set geo_id = (select gd.GEO_ID from geo_dim gd
left outer join Target_Segment T ON T.[Geography_Name]= GD.SubsidiaryName
WHERE T.GEOGRAPHY_LEVEL_NAME ='Subsidiary')
Initially geo_id is set to null during ETL , but later it needs to be updated using update statement. Please let me know an appropriate solution for the same. Thanks in Advance
Cheers!
Abhishek
June 29, 2011 at 2:54 am
The cause of the error is pretty self-evident, so I won't labour that point. Using the MERGE syntax may help you write the query more naturally, and make it easier for you to see where you are going wrong.
MERGE Target_Segment AS ts
USING geo_dim AS gd
ON gd.SubsidiaryName = ts.Geography_Name
WHEN MATCHED
AND ts.GEOGRAPHY_LEVEL_NAME ='Subsidiary'
THEN UPDATE SET geo_id = gd.GEO_ID;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 29, 2011 at 3:38 am
Awesome man....Thanks a lot:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply