SQL Server Performance Forum – Threads Archive

Update a table from another one filtering by third

Tell me please how can I make a query that updates one table using data from another but not all and not filtered by its field but filtered by a field in a linked table. I’ve tryed to use usual construction but it doesn’t work:
UPDATE arch_exm SET reg_num = t1.reg_num, dscp = t1.dscp, checktype = t1.contr_type, mark = t1.mark, contr_date = t1.d_kontr, semester = t1.semester, n_register = t1.n_regist FROM (ses_curr INNER JOIN students ON ses_curr.reg_num = students.reg_num INNER JOIN groups ON students.stgroup = groups.stgroup AND students.stgroup = groups.stgroup INNER JOIN specialties ON groups.speciality = specialties.speciality AND groups.speciality = specialties.speciality) t1 WHERE (t1.arch_store = 1) AND (t1.depart = @depart)
So I need to use for update source a table is produced by joining several ones and use a filter in one of its fields but in syntax FROM <table> <name> is supported only one table but not a result of joining. Tell me please how can I describe in SQL using after FROM a result of joining with a filter. cogito ergo sum

---

The brackets in the FROM clause suggest that this query was started in Access, right? You don’t need brackets if SQL Server is handling the query. I always find this is the best way: set up a SELECT query that collects all the right information, using the right JOINs and WHERE statement (perhaps including a correlated subquery). Then you change the SELECT clause to the appropriate UPDATE clause, and you’re done.

---

Thank you, using SELECT as subquery helps. cogito ergo sum

---

]]>