Update SQL Server table where column matches another table-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

I figured it out

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

Thank you everyone