-1

I am new to this and programming and wondering if someone could help me with a sql query. I have 3 tables. table 1 is called Student(id,number,teacherID) and table 2nd table is called StudentUpdate (StudentId, teacher_number) and the third table is called teacher (id, number,studentID).

I would like to update table 1 (student table) with all the values from table 2 (student update) based on the teacherID . how do I do that when table 2 contains the teacher number not the ID ? I am using SQL server 2012. Thanks to all in advance

Maz Baz
  • 17
  • 1
  • 7
  • Anything you've tried so far? – BJones Jun 06 '16 at 19:14
  • What you're looking for is the concept of JOINS. There are a couple different types of Joins and I recommend you do some serious research on them; they can get very complex but are one of the main building blocks for SQL statements. Giorgos Betsos provided an example below but, again, really focus on understanding what the JOIN is doing. In the case below, an `INNER JOIN` is being used. Compare that to an `OUTER JOIN`. –  Jun 06 '16 at 19:19
  • 1
    Possible duplicate of [How can I do an UPDATE statement with JOIN in SQL?](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) – Ben Jun 06 '16 at 19:23
  • The teacher table has one row for every Student-Teacher combination? Why do you even have the StudentUpdate table then? Is this a design you are forced to work with, or can you change it? – Tab Alleman Jun 06 '16 at 19:37

1 Answers1

0

You can use the following query:

UPDATE s
SET TeacherID = su.teacher_number
FROM Student AS s
JOIN Teacher AS t ON s.teacherID = t.id 
JOIN StudentUpdate AS su ON s.id = su.StudentId

The above query performs an UPDATE using two JOIN operations:

  • Student with Teacher based on the ids of the teacher and

  • StudentUpdate with Teacher based on the id of the student.

    so as to extract from table StudentUpdate the info required for the UPDATE (based on the limited info given in the OP).

Community
  • 1
  • 1
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • Sorry for the -1 but answers with no explanation promote copy+paste coding. Can you provide an explanation of the JOINS you're using? –  Jun 06 '16 at 19:21
  • number = t.number, isnt it wrong to join on 2 different keys? you are joining student number to teacher number? – Maz Baz Jun 06 '16 at 19:30
  • @GiorgosBetsos student has (an ID PK, Student number and TeacherID FK). Teacher has ID PK, TeacherNumber ) . The StudentUpdate contains the studentID and Teacher.Number Not teacher.ID :( . I want to update the Student.TeacherID in the student table based on the teacher number provided in the StudentUpdate table. It would have been alot easier if they have provided me with teacherID in StudentUpdate table . Sorry for the confusion – Maz Baz Jun 06 '16 at 19:50
  • @GiorgosBetsos i get error converting data type nvarchar to bigint. line SET TeacherID = su.teacher_number – Maz Baz Jun 06 '16 at 20:17
  • @MazBaz What is the actual value that you are trying to update? – Giorgos Betsos Jun 06 '16 at 20:18
  • @GiorgosBetsos I want to update the Student Table . teacher ID column from the student update table. but the student update table carries the teacheer Number not the teacher ID – Maz Baz Jun 06 '16 at 20:23
  • The student update table is a temporary table . the data is fed from a another department. – Maz Baz Jun 06 '16 at 20:30
  • @MazBaz The error you get is because there is an improper value in `StudentUpdate` table. By *improper* I mean a value that cannot be converted to a `bigint`. – Giorgos Betsos Jun 06 '16 at 20:40