0

I have table Student with a column called IdStudent. The value of IdStudent is 0

Table Student also have a column called UID

I need to update IdStudent in table Student with IdCandidate in table Candidate.

Table Candidate also have UID column containing the same UID of table Student.

So we can do this to have IdCandidate: 

 select C.IdCandidate from Candidate as C inner join Student as S
 on C.UID = S.UID

How can I update IdStudent in table Student with this IdCandidate obtained in this select?

Thanks!

ADM
  • 1,590
  • 11
  • 36
  • 54

2 Answers2

1

Use JOIN in update

update s set s.IdStudent = C.IdCandidate
from Candidate as C 
inner join Student as S on C.UID = S.UID
Shushil Bohara
  • 5,556
  • 2
  • 15
  • 32
-1

You can do it using the following query :

update S set S.IdStudent = C.IdCandidate
    from Student S 
        inner join Candidate C on S.UID=C.UID
Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
  • Avoid posting duplicate answers. I am not getting any difference between your and mine answer. – Shushil Bohara Jan 12 '18 at 10:56
  • Please check deeply, there is difference :) – Md. Suman Kabir Jan 12 '18 at 10:58
  • Changing table names in the clause makes difference for you, if so then why don't you answer every questions by copying others and just swapping the table names in the join. – Shushil Bohara Jan 12 '18 at 11:10
  • In fact you copied! You just made the update query by copying the relation from the OP's query, but you should notice that OP's select query has the main table `Candidate`, but he wants to update the table `Student`, so the update query should have `Student` as the first table in the `FROM` clause, this is standard sql, you can learn more about these type of query [here](https://stackoverflow.com/a/224740/5695953) , [here](https://stackoverflow.com/a/2334741/5695953) and you can google more. Thanks anyway. – Md. Suman Kabir Jan 12 '18 at 11:20
  • @MuhammadUsman - Putting table names in correct order in `JOIN` queries is important, please see properly, the query i provided is the standard query, not the accepted one's. To learn, you can follow my above comments here. – Md. Suman Kabir Jan 12 '18 at 11:32
  • I have visited your links but these are just answers and the standard you are talking about is not mentioned anywhere in these links. Even if it's the standard it would better not to answer by making that difference. It's my suggestion that you obey or not, doesn't matter to me. – Shushil Bohara Jan 12 '18 at 11:40
  • I just answered following standard sql, the links you saw are heavily upvoted because they are useful and followed standard sql. You can do more study on it and i am sure you will agree finally. Again i just answered the OP's question, not a correction of your query. – Md. Suman Kabir Jan 12 '18 at 11:55