title: How should I update existing table in the SQL Server database via C# and Windows forms?
body: <p>I want to use Windows Forms and C# to implement a Database application which consists of the following tables:</p>
  <p><code>Student</code> table:</p>
  <pre><code>CREATE TABLE [dbo].[Student] 
  (
      [Id]   INT          NOT NULL,
      [Name] NVARCHAR(50) NOT NULL,
      [MyId] AS ('S' + RIGHT('00' + CONVERT([varchar](5), [Id]), (2))) PERSISTED,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  </code></pre>
  <p><code>Class</code> table:</p>
  <pre><code>CREATE TABLE [dbo].[Class] 
  (
      [Id]      INT          NOT NULL,
      [Teacher] NVARCHAR(50) NOT NULL,
      [Grade]   INT          NOT NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  </code></pre>
  <p><code>StudentClassCombo</code>:</p>
  <pre><code>CREATE TABLE [dbo].[StudentClassCombo]  
  (
      [ClassID]   INT NOT NULL,
      [StudentID] INT NOT NULL,
      CONSTRAINT [ClassFK] 
          FOREIGN KEY ([ClassID]) REFERENCES [dbo].[Class] ([Id]),
      CONSTRAINT [StudentFK] 
          FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Student] ([Id])
  );
  </code></pre>
  <p>I have a Windows forms interface through which I can assign students to classes.</p>
  <p><a href="https://i.stack.imgur.com/iGZId.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iGZId.png" alt="enter image description here" /></a></p>
  <p>I want to ensure that when the a student that has already been assigned to a class is re-assigned to a different class. the previous student-class assignment should be overwritten with the new one. In the case above, if Student ID 1 is already assigned to Class ID 1. But if the user decides to re-assign Student ID 1 to Class ID 2, the existing StudentClassCombo entry of 1-1 should be changed to 1-2.</p>
  <p>I have written the following code to perform this update but I am encountering an exception:</p>
  <pre><code>string UpdateQuery = @&quot;UPDATE dbo.StudentClassCombo SET&quot;
                        + &quot; Class.ID as ClassId, Student.Id as StudentId FROM dbo.Class, dbo.Student&quot; +
                        &quot; WHERE Class.Grade=@Grade and Student.Name LIKE @StudentName&quot;;
  
  
  using (connection = new SqlConnection(connectionString))
  using (SqlCommand Insertcmd = new SqlCommand(UpdateQuery, connection))
  {
      connection.Open();
  
      Insertcmd.Parameters.Add(&quot;@Grade&quot;, SqlDbType.Int);
      Insertcmd.Parameters.Add(&quot;@StudentName&quot;, SqlDbType.NVarChar, 50);
  
      foreach (ListViewItem eachItem in StudentsList.CheckedItems)
      {
          Insertcmd.Parameters[&quot;@Grade&quot;].Value = int.Parse(ClassNames.Text);
          Insertcmd.Parameters[&quot;@StudentName&quot;].Value = eachItem.SubItems[1].Text.ToString();
                      
          Insertcmd.ExecuteNonQuery();
      }
  
      connection.Close();
  }
  </code></pre>
  <p>The exception I am seeing now is as follows:</p>
  <blockquote>
  <p>An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll</p>
  <p>Additional information: Incorrect syntax near the keyword 'as'.</p>
  </blockquote>
  <p>How should I update the <code>StudentClassCombo</code> entries?</p>