Sql updating a table from the same table


07-Aug-2019 16:00

If not, is there another way I can write this update sql to achieve the same affect? EDIT: I think I got it to work: UPDATE user_account student LEFT JOIN user_account teacher ON teacher.user_account_id = student.teacher_id SET student.student_education_facility_id = teacher.education_facility_id WHERE student.user_type = 'ROLE_STUDENT'; UPDATE user_account student INNER JOIN user_account teacher ON teacher.user_account_id = student.teacher_id AND teacher.user_type = 'ROLE_TEACHER' SET student.student_education_facility_id = teacher.education_facility_id It would be nice if there were two solutions: the specific and one a bit more abstract, with clearer table and column names, to simplify reading for people looking for a general answer, like me.

In any case, both question and answer satisfy me, but I was suggesting it for a better understanding of the community.

Stack Exchange network consists of 171 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

UPDATE user_account SET (student_education_facility_id) = ( SELECT teacher.education_facility_id FROM user_account teacher WHERE teacher.user_account_id = teacher_id AND teacher.user_type = 'ROLE_TEACHER' ) WHERE user_type = 'ROLE_STUDENT' Above are the sample update query...

Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.