Update multiple rows from select statement

Question

I am trying to assign 'A' to [Student Details].group based on this SELECT statement.

SELECT     TOP (10) PERCENT [Person Id], [Given Names], Surname, Gpa, [Location Cd]
FROM         [Student Details]
WHERE     ([Location Cd] = 'PAR')
ORDER BY Gpa DESC

I can't figure out how to use a SELECT statement in an UPDATE statement. Can someone please explain how to accomplish this?

I am using ASP .NET and MsSQL Server if it makes a difference.

Thanks

Possible duplicate http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — Jitendra Pancholi, Oct 17 '13 at 07:46
Do you need to update the table or just a select with `'A'` as group? — Kaf, Oct 17 '13 at 07:46
Currently Group is null.. I need to update group to contain 'A' for all of the rows that are returned from the above query — user2427023, Oct 17 '13 at 07:49

score 1 · Answer 1 · answered Oct 17 '13 at 08:01

I'm assuming you want to update these records and then return them :

SELECT TOP (10) PERCENT [Person Id], [Given Names], Surname, Gpa, [Location Cd]
INTO #temp
FROM [Student Details]
WHERE     ([Location Cd] = 'PAR')
ORDER BY Gpa DESC

update [Student Details] set group='A' where [person id] in(select [person id] from #temp)

select * from #temp

I'm also assuming person id is the PK of student details

Jitendra Pancholi · Answer 2 · 2013-10-17T07:59:22.953

0

Is this you want?

Update top (10) Percent [Student Details] set [group] = 'A' 
where [Location Cd] = 'PAR' AND [group] is null

edited Oct 17 '13 at 07:59

answered Oct 17 '13 at 07:53

Jitendra Pancholi

7,897
12
51
84

No sorry, it must sort the rows based on GPA and only assign group='A' to the top 10% Thanks for the reply though. – user2427023 Oct 17 '13 at 07:56
Sorry still wouldn't work.. It needs to sort the table in descending order from the column 'gpa' – user2427023 Oct 17 '13 at 08:01

Kaf · Accepted Answer · 2013-10-17T08:10:15.647

0

Try this using CTE (Common Table Expression):

;WITH CTE AS
(
    SELECT TOP 10 PERCENT [Group]
    FROM      [Student Details]
    WHERE     ([Location Cd] = 'PAR')
    ORDER BY Gpa DESC
)
UPDATE CTE SET [Group] = 'A'

edited Oct 17 '13 at 08:10

answered Oct 17 '13 at 07:56

Kaf

33,101
7
58
78

thanks, but I'm getting incorrect syntax near GROUP and FROM :( – user2427023 Oct 17 '13 at 08:00
There is no syntax error as far as I can see. [Check this similar example](http://sqlfiddle.com/#!3/046e1/3) with no such syntax errors. May be you are missing angle brackets `'[]'` around group which is a key word. – Kaf Oct 17 '13 at 08:05
1

ah you're a champion, the problem was that there was 2 of the word BY :) Apart from that, it worked :D Thank you. – user2427023 Oct 17 '13 at 08:09
Early morning hear and the engine is still warming up ! :) – Kaf Oct 17 '13 at 08:11

Update multiple rows from select statement

3 Answers3