Selecting an updating the same record in one query-CodePudding

There are identical questions here, but I don't understand how I would be able to apply the suggested solutions to my problem, so here goes...

I have the following code where I want to change the StudentID from 7 to 8

UPDATE Student
SET StudentID = 8, Forename = "Adam", Surname = "Sai", YearID = 1,
WHERE StudentID = 7

This is in the context of a Tkinter treeview where I want to be able to update any value which is selected. Tkinter Treeview

I get the following error:

Execution finished with errors.

Result: near "WHERE": syntax error At line 1: UPDATE Student

                SET StudentID = 7, Forename = "Adam", Surname = "Sai", YearID = 1,
                WHERE

I am wondering whether this is because I am trying to update and identify the record both using StudentID.

CodePudding user response：

The syntax error in your query is the comma before the where clause. Eliminate it and try again.

CodePudding user response：

Try using this.

  Update Student

SET

StudentID = Replace(StudentID,'7','8');