Update SQL Server table where column matches another table

Question

I am new to SQL Server and I have these 2 tables:

StudentTable      |      ClassTable

StudentId         |      ClassId       
StudentCode       |      StudentCode
StudentName       |      Class
Class             |

Currently a StudentTable record looks like this:

StudentId    StudentCode    StudentName    Class
-------------------------------------------------
1            20221452       James Daniels  Null

and a ClassTable record looks like

ClassId     StudentCode    Class
----------------------------------
1           20221452       A

I was wondering if it possible to do an update select for class where StudentCode in ClassTable matches StudentCode in StudentTable

I have tried this statement but it looks very wrong and just threw a syntax error

  INSERT INTO [StudentTable](Class)
      SELECT Class
      FROM [ClassTable ]
      WHERE StudentCode IN ([StudentTable]) = StudentCode in ([ClassTable])

EDIT: turns out I was looking for an update as I had a few records currently existing

You have an `IN` equal an `IN`; what is that trying to achieve. The syntax of an `IN` is either `{Scalar Expression} IN ({Delimited List of Literal Values})` or `{Scalar Expression} IN ({SELECT Statement returning single column})` — Thom A, Sep 14 '22 at 08:59
This question is a duplicate of: [Update with two tables?](https://stackoverflow.com/questions/1068447/update-with-two-tables) — Luuk, Sep 14 '22 at 11:47

score 0 · Answer 1 · answered Sep 14 '22 at 09:12

Can use EXISTS keyword

 insert into [StudentTable](Class)
  select Class
  FROM [ClassTable ]
  WHERE EXISTS(SELECT 1 FROM [StudentTable] WHERE StudentTable.StudentCode = ClassTable.StudentCode)

For more info about EXISTS keyword, please read this EXISTS

If you want to use IN keyword, you can write a subquery after IN keyword.

score 0 · Answer 2 · answered Sep 14 '22 at 09:41

0

If I understood your question correctly, you could use join easily.

 insert into [StudentTable] (Class)
 select class from studentable join classtable on
 studentable.StudentCode  =classtable.StudentCode

answered Sep 14 '22 at 09:41

Mandana

46
3

score 0 · Answer 3 · answered Sep 14 '22 at 09:49

0

I figured it out

UPDATE StudentTable
SET StudentTable.Class=ClassTable.Class
FROM StudentTable, ClassTable
WHERE StudentTable.StudentCode=ClassTable.StudentCode;

Thank you everyone

answered Sep 14 '22 at 09:49

Kibofigs

15
6

Update SQL Server table where column matches another table

3 Answers3