looping through query - 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)

looping through query

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-26 : 03:24:33

declare @childtimes as table (id int,class nvarchar(50),classid int,childname nvarchar(80),childid int)

insert into @childtimes (childname,childid)
select surname +','+forename,childid from children c left join families f on c.familyid=f.familyid where (classid=6 or classid=1 or classid=2 or classid=3 or classid=7 or classid=8) and c.familyid=@familyid

update @childtimes set classid=classid from childrenclass c where c.childid=childid

with this last statement i get an error
Ambiguous column name 'childid'.

what is the right way to reference childid I want to update my temporary table with the correctclassid based on the childid in childrenclass = to the childid in @childtimes

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-26 : 03:29:10

update ct
set classid=classid
from @childtimes ct
join childrenclass c on c.childid=ct.childid

No, you're never too old to Yak'n'Roll if you're too young to die.