SQL Server Performance Forum – Threads Archive

Converting SELECT Query to Update

I am using the data mapping below to get the ‘UnderGraduateInstitution’. I NEED to change thie SELECT query Into an UPDATE Query. I tried it 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’ ============================================>
–My Update query—-> IS this CORRECT 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’

---

The General Way is
Update T1 Set T1.col1=T1.col1
from firstTable T1 inner join (LongSelectStatement) T2
on T1.col1=T2.col1 and T1.col2=T2.col2 — and other required conditions using Where clause Madhivanan Failing to plan is Planning to fail

---

A few problems with your update query — it looks as if you copied a few JOIN statements two times over. If you run the SELECT version of the query, how many rows are returned? If it is more than one, then I guess in the UPDATE version the VueEducationProgram.Name value from the last row will be written into @tmpCandidate (whichever is the last row – totally unpredictable). The problem of multiple rows being returned is most likely caused by this JOIN with the two ORs: 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) Test it with just one set of matching fields, like: FROM Vuedf_fields
INNER join Vueclients
ON VueClients.Screen_id = Vuedf_fields.Screen_ID

---

]]>