如何对具有两个相似字段和一个不同字段的两个表执行UPDATE操作？ [英] How to perform UPDATE operation on two tables having two similar fields and one different field?

问题描述

我有两个表格名为 studentBio 和主题。两个表的字段在下面给出（有一些值）：在表单上我有这样的：

AND 在 checkedlistbox1 中以类似A + B + C的形式显示相应主题。

$ b $
b

1. studentBio 表格的字段如下： WHERE （RollNo，RegYear，计划和教职员工合并为 >主键）：

     RollNo RegYear stuName program faculty phoneNuber地址
    
    1 2010 John Intermediate Pre-工程343483834伦敦
    
    2 2011 Leonard中级预医疗454545445纽约
    
    3 2012亨利毕业BA 565656565奥斯陆
     




2. 主题表的示例字段如下： WHERE
   （RollNo，RegYear，程序和教师结合使用组合
   主键）：

     RollNo RegYear程序faculty subjectName 
    
    1 2010中级预工程A 
    1 2010中级预工程B 
    1 2010中级预工程C 
    2 2011中级预医
    2 2011中级前医疗E 
    2 2011中级前医疗F 
     







等。现在来看问题。我所做的是主题表中的更新（教师和计划）和主题N 。到目前为止，我已经更新只有 studentBio 表，这很容易，但我无法弄清楚如何构建我的更新查询更新科目表？

解决方案

如果我理解得很好，请尝试这个重组版本的查询：

  string update_Personal =UPDATE studentBio INNER JOIN subject ON studentBio.RollNo = subjects.RollNo AND studentBio.RegYear = subjects.RegYear AND studentBio。 program = subjects.program AND studentBio.faculty = subjects.faculty SET studentBio.program = ProgramU，studentBio.faculty = FacultyU，subjects.subjectName = SubjectNameU WHERE studentBio.RollNo =+ Convert.ToInt32（this.rollNumber7_combo.SelectedItem.ToString ））+AND studentBio.RegYear =+ Convert.ToInt32（this.comboBox3.SelectedItem.ToString（））+AND studentBio.program ='+ this.comboBox1.SelectedItem.ToString（）+'和studentBio。 faculty ='+ this.comboBox2.SelectedItem.ToString（）+'; 
  

我添加了以下部分以更新 subject 表：




1. studentBio INNER JOIN subject ON studentBio.RollNo = subjects.RollNo AND studentBio.RegYear = subjects.RegYear AND studentBio.program = subjects.program AND studentBio.faculty = subjects.faculty - 此部分加入 subject 表允许更新




2. ，subjects.subjectName = SubjectNameU - SET 阻止更新主题表中的subjectName列




3. WHERE studentBio.RollNo =+ Convert.ToInt32 （this.rollNumber7_combo.SelectedItem.ToString（））+AND studentBio.RegYear =+ Convert.ToInt32（this.comboBox3.SelectedItem.ToString（））+AND studentBio.program ='+ this.comboBox1.SelectedItem。 ToString（）+'AND studentBio.faculty ='+ this.comboBox2.SelectedItem.ToString（）+' - 最后一部分是 WHERE 块，其中前缀 studentBio。被添加到每个键列以精确地应用于哪个表，应该应用过滤（因为两个表具有相同的键列名）




我希望它能以某种方式帮助你。




I have got two tables named "studentBio" and "subjects". Fields of both tables are being given below(with some values): On the form i have got something like this: AND in checkedlistbox1 i am showing corresponding subjects in the form like A+B+C. Which after retrieving from user would be split on the basis of '+' and be added into subjects table all at once.

1. Fields of studentBio table are given as follows: WHERE (RollNo, RegYear,program, and faculty combine to make composite primary key):

   RollNo     RegYear   stuName     program       faculty         phoneNuber    Address
   
   1         2010      John       Intermediate  Pre-Engineering  343483834    London
   
   2         2011      Leonard    Intermediate  Pre-Medical      454545445    NewYork
   
   3         2012      Henry      Graduation    B.A              565656565    Oslo
   

2. Similary fields of subjects table are as follows: WHERE (RollNo, RegYear,program, and faculty combine to make composite primary key):

   RollNo     RegYear   program       faculty           subjectName 
   
   1          2010     Intermediate   Pre-Engineering    A
   1          2010     Intermediate   Pre-Engineering    B
   1          2010     Intermediate   Pre-Engineering    C
   2          2011     Intermediate   Pre-Medical        D
   2          2011     Intermediate   Pre-Medical        E
   2          2011     Intermediate   Pre-Medical        F
   

and so on. Now lets come to the problem. What i am doing is UPDATING (faculty and program) in both tables and subjectN in subjects table. What i have done so far is that i have updated only studentBio table which is quite easy but i can't figure-out as how could i structure my update query to update subjects table? Can somebody please help me structuring the query?

解决方案

If I understood well, please try this restructured version of your query:

string update_Personal = "UPDATE studentBio INNER JOIN subjects ON studentBio.RollNo = subjects.RollNo AND studentBio.RegYear = subjects.RegYear AND studentBio.program = subjects.program AND studentBio.faculty = subjects.faculty SET studentBio.program = ProgramU, studentBio.faculty = FacultyU, subjects.subjectName = SubjectNameU WHERE studentBio.RollNo = " + Convert.ToInt32(this.rollNumber7_combo.SelectedItem.ToString()) + " AND studentBio.RegYear = " + Convert.ToInt32(this.comboBox3.SelectedItem.ToString()) + " AND studentBio.program = '" + this.comboBox1.SelectedItem.ToString() + "' AND studentBio.faculty = '" + this.comboBox2.SelectedItem.ToString() + "'";

I've added the following parts to make it update subjects table:

1. studentBio INNER JOIN subjects ON studentBio.RollNo = subjects.RollNo AND studentBio.RegYear = subjects.RegYear AND studentBio.program = subjects.program AND studentBio.faculty = subjects.faculty - this part joins the subjects table making it's update possible

2. , subjects.subjectName = SubjectNameU - fragment in the SET block updating subjectName column in subjects table

3. WHERE studentBio.RollNo = " + Convert.ToInt32(this.rollNumber7_combo.SelectedItem.ToString()) + " AND studentBio.RegYear = " + Convert.ToInt32(this.comboBox3.SelectedItem.ToString()) + " AND studentBio.program = '" + this.comboBox1.SelectedItem.ToString() + "' AND studentBio.faculty = '" + this.comboBox2.SelectedItem.ToString() + "'" - the last part is the WHERE block in which prefix studentBio. was added to each key column to precise to which table the filtering should be applied (because both tables ha the same key column names)

I hope it could help you in some way.

这篇关于如何对具有两个相似字段和一个不同字段的两个表执行UPDATE操作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！