Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    12

    Question Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, clean up your code by dumping the subtable/alias thing and dropping superfluous parentheses:

    Code:
    UPDATE	arch_exm
    SET	reg_num = reg_num,
    	dscp = dscp,
    	checktype = contr_type,
    	mark = mark,
    	contr_date = d_kontr,
    	semester = semester,
    	n_register = 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
    WHERE	arch_store = 1
    	AND depart = @depart
    Now you can see that nowhere in your FROM clause do you include any link to arch_exm, the table you are updating. How is arch_exm related to ses_curr, students, groups, and/or specialties?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •