CODEWITHSUNDEEP
mysql
Boladale Kolade
Boladale Kolade

Reputation: 1

is it possible to add a select and a conditional clause to an update statement

i have a database of students exam records based on year and semesters and i want to make changes to the level of all students in a particular year and semester with the following code which gave me an error.

update result
set level = ( select new_level.student 
               from new_level
               where new_level.student = result.student
              limit 1
              )
and year = '2016/2017'
and semester = 2

will appreciate some help on resolving this. thank you

Upvotes: 0

Views: 29

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You need a where clause and an update with join for join the student at both the involved tables 

update result
inner join  new_level on new_level.student = result.student
set result.level =  new_level.student 
where year = '2016/2017'
and semester = 2

or you can directly add the condition to the ON clause (avoinding the where word ) eg:

update result
inner join  new_level on new_level.student = result.student 
       and year = '2016/2017'
         and semester = 2
set result.level =  new_level.student

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

You missed the WHERE in the UPDATE statement. 

update result
set level = ( select new_level.student 
               from new_level
               where new_level.student = result.student
              limit 1
              )
where #what you missed.
    year = '2016/2017'
  and
    semester = 2

Upvotes: 1

Related Questions