Update from Subselect Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update from Subselect Issue

Author

Topic

lueylump
Starting Member

25 Posts

Posted - 2011-02-04 : 16:46:40

I am receiving the following error when I attempt the execute an Update statement from a subselect to a temporary table that also accesses that table twice within the subselect:

Msg 207, Level 16, State 1, Line 74
Invalid column name 'Group_ID'.

Update #Donor_Summary
Set Org_Name = Parent_Nm,
	Group_Cd = Parent_Cd,
	Soft_Credit_Grp_Nm = Child_Nm,
	Soft_Credit_Grp_Cd = Child_CD,
	Solicitor_Nm = Solicitor 
from
(  
Select Q.Group_ID as Child_ID, Q.Group_Cd as Child_Cd, Q.Org_Name as Child_Nm,
		R.Group_Cd as Parent_Cd, R.Org_Name as Parent_Nm, R.Solicitor_Nm as Solicitor
	from #Donor_Summary Q
	INNER JOIN Related_Groups P ON P.Rel_Type_Code = 1270005 and P.Child_Group_ID = Q.Group_ID
	INNER JOIN #Donor_Summary R on R.Group_ID = P.Parent_Group_ID
) Z
where #Donor_Summary.Group_ID = Z.Group_ID

Any assistance is appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-04 : 16:48:55

where #Donor_Summary.Group_ID = Z.Child_ID

By the way, it's known as a derived table rather than a subselect.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lueylump
Starting Member

25 Posts

Posted - 2011-02-04 : 16:55:45

Thanks Tara for the solution as well as the SQL lesson. By the way I tried your solution but it gave me a different error and I did not look at it closely enough to see the difference in messages. I really appreciate you helping us novices!!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-04 : 16:59:08

Please post the new error message.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-07 : 05:05:28

Try this


Update D
Set Org_Name = Parent_Nm,
	Group_Cd = Parent_Cd,
	Soft_Credit_Grp_Nm = Child_Nm,
	Soft_Credit_Grp_Cd = Child_CD,
	Solicitor_Nm = Solicitor 
from #Donor_Summary as D inner join
(  
Select Q.Group_ID as Child_ID, Q.Group_Cd as Child_Cd, Q.Org_Name as Child_Nm,
		R.Group_Cd as Parent_Cd, R.Org_Name as Parent_Nm, R.Solicitor_Nm as Solicitor
	from #Donor_Summary Q
	INNER JOIN Related_Groups P ON P.Rel_Type_Code = 1270005 and P.Child_Group_ID = Q.Group_ID
	INNER JOIN #Donor_Summary R on R.Group_ID = P.Parent_Group_ID
) Z
ON D.Group_ID = Z.CHILD_ID

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources