SQL Server Performance Forum – Threads Archive

Update Query6 Help (JOINS)

I have all possible joins in the update statement below. How would I get it optimized. I wish to update @tmpCandidate.UndergradInstitution. I am joining on FIVE tables — VueEducationProgram, Vuedf_fields, Vueclients, VueClientQuestions, @tmpCandidate, VueStudentsBeta. Any help will be appreciated. Thanks!
—Update query—-> UPDATE @tmpCandidate
SET UndergradInstitution = VueEducationProgram.Name
FROM Vuedf_fields
INNER join Vueclients
ON (VueClients.Screen_id = Vuedf_fields.Screen_ID
OR VueClients.ExamRegScreen_ID = Vuedf_fields.Screen_ID
OR VueClients.ProductScreen_ID = Vuedf_fields.Screen_ID)
INNER JOIN VueClientQuestions
ON VueClientQuestions.Field_ID = Vuedf_fields.Field_ID
INNER JOIN @tmpCandidate AS tmpCandidate
ON tmpCandidate.Client_ID = Vueclients.Client_ID
ON VueClientQuestions.Student_ID = tmpCandidate.Student_ID
INNER JOIN VueEducationProgram
ON VueEducationProgram.Client_ID = Vueclients.Client_ID
INNER JOIN VueClientQuestions
ON VueClientQuestions.Client_ID = VueClients.Client_ID
INNER JOIN @tmpCandidate AS tmpCandidate
ON @tmpCandidate.Client_ID = VueClientQuestions.client_ID
INNER JOIN VueEducationProgram
ON VueEducationProgram.Client_ID = VueClientQuestions.client_ID
INNER join VueStudentsBeta
ON VueStudentsBeta.Student_ID = VueClientQuestions.Student_ID
INNER JOIN @tmpCandidate AS tmpCandidate
ON tmpCandidate.Student_ID = VueStudentsBeta.Student_ID
INNER join VueEducationProgram
ON VueEducationProgram.client_ID = tmpCandidate.client_ID
AND VueEducationProgram.Client_ID=388
WHERE Vuedf_fields.FieldName = ‘UnderGradInstitution’

What’s wrong with the statement? Without knowing your schema, I think, it’s hard to tell if your statement can be improved. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

I am using the data mapping below to get the ‘UnderGraduateInstitution’. I NEED to change thie SELECT query Into an UPDATE Query. I tried this above but failed to update the column. Thanks! —>convert the code below to an update query. Select
EducationProgram.Name as ‘UnderGraduateInstitution’
from df_fields
inner join clients
on Clients.Screen_id = df_fields.Screen_ID
or Clients.ExamRegScreen_ID = df_fields.Screen_ID
or Clients.ProductScreen_ID = df_fields.Screen_ID
inner join ClientQuestions
inner join EducationProgram
on ClientQuestions.Value = EducationProgram.EducationProgramCode AND EducationProgram.Client_ID=388
on df_fields.Field_ID = ClientQuestions.Field_ID
inner join Students
on ClientQuestions.Student_ID = Students.Student_ID
Where df_fields.FieldName = ‘UnderGradInstitution’

]]>