3

I'm trying to update CurrentLevel on table FSB_ProcessStudents From Students table based on StudentId
that's my statement 

UPDATE FSB_ProcessStudents SET Currentlevel = 
(SELECT 
    s.LevelId
    From FSB_Students s
    INNER JOIN FSB_ProcessStudents ps
         ON ps.StudentId = s.StudentId)

on Excution it gives me the error :

Subquery returned more than 1 value.

where did I go wrong?

Amr Ibrahim
  • 167
  • 3
  • 14

5 Answers5

6

Sql server supports the use of joins in update statements, so you can write your update statement like this:

UPDATE ps
SET Currentlevel = s.LevelId
FROM FSB_Students s
INNER JOIN FSB_ProcessStudents ps
     ON ps.StudentId = s.StudentId
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

Try the below query

UPDATE  ps
SET     ps.Currentlevel = s.LevelId
FROM    FSB_ProcessStudents ps
    INNER JOIN FSB_Students s   on s.StudentId = ps.StudentId
Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
1

Use below query for update :

UPDATE FSB_ProcessStudents SET Currentlevel = LevelId
FROM FSB_Students S
WHERE FSB_ProcessStudents.StudentId = s.StudentId
Shahzad Barkati
  • 2,532
  • 6
  • 25
  • 33
Mansoor
  • 4,061
  • 1
  • 17
  • 27
0

You are getting this issue because your sub query return multiple value and it is not possible to update multiple value by single update statement.

So Use top key word like below and it will be resolved 

UPDATE FSB_ProcessStudents SET Currentlevel = 
(SELECT top 1
    s.LevelId
    From FSB_Students s
    INNER JOIN FSB_ProcessStudents ps
         ON ps.StudentId = s.StudentId order by ps.StudentId desc)
Anurag_Soni
  • 542
  • 2
  • 17
0

The error is self-explanatory. Your subquery should return only one value.

Try this one instead:

UPDATE FSB_ProcessStudents SET Currentlevel = 
(SELECT TOP 1
    s.LevelId
    From FSB_Students s
    INNER JOIN FSB_ProcessStudents ps
         ON ps.StudentId = s.StudentId)

Or, make sure you add other conditions to return one or none values.

CristiC
  • 22,068
  • 12
  • 57
  • 89
  • Using `TOP x` without an `ORDER BY` clause acutally means "give me one random record from the table". You should never do it. – Zohar Peled Feb 28 '17 at 10:45
  • Not random :). But this was just a way to illustrate the reason for the error. Other conditions should probably added, to make sure the correct value is returned. – CristiC Feb 28 '17 at 10:49
  • But I agree with you @Zohar Peled - TOP without an ORDER BY should not be used. – CristiC Feb 28 '17 at 10:50
  • Well, how would you call it if not *random*? The order of the records in a select statement without an `order by` clause is not guaranteed in any rdbms, since database tables are un ordered by nature. It may not be as random as `order by newID()` but it sure as hell aint something reliable. – Zohar Peled Feb 28 '17 at 10:51
  • Random for me means in this case that when I run the query, 99.99% of time will return other results. But instead, not specifying an ORBER BY, will make the results come depending on the plan used at that time by the rdbms engine. And there are high chances that consecutive runs will use the same plan. So, not random :) – CristiC Feb 28 '17 at 11:10
  • Well, English is not my native language so perhaps I'm un aware of a better term, but I understand your point and agree with you. – Zohar Peled Feb 28 '17 at 11:15