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

Do you need to update the table or just a select with 'A' as group? — Indi
– Indi, Commented Oct 17, 2013 at 7: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
– user2427023, Commented Oct 17, 2013 at 7:49

Gary W · Accepted Answer · 2013-10-17 08:01:54Z

1

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

answered Oct 17, 2013 at 8:01

Gary W

1,9241 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Indi · Accepted Answer · 2013-10-17 08:10:15Z

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, 2013 at 8:10

answered Oct 17, 2013 at 7:56

Indi

33.9k7 gold badges61 silver badges80 bronze badges

4 Comments

user2427023 Over a year ago

thanks, but I'm getting incorrect syntax near GROUP and FROM :(

Indi Over a year ago

There is no syntax error as far as I can see. Check this similar example with no such syntax errors. May be you are missing angle brackets '[]' around group which is a key word.

user2427023 Over a year ago

ah you're a champion, the problem was that there was 2 of the word BY :) Apart from that, it worked :D Thank you.

Indi Over a year ago

Early morning hear and the engine is still warming up ! :)

Jitendra Pancholi · Accepted Answer · 2013-10-17 07:59:22Z

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, 2013 at 7:59

answered Oct 17, 2013 at 7:53

Jitendra Pancholi

7,62012 gold badges54 silver badges85 bronze badges

2 Comments

user2427023 Over a year ago

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 Over a year ago

Sorry still wouldn't work.. It needs to sort the table in descending order from the column 'gpa'

Collectives™ on Stack Overflow

Update multiple rows from select statement

3 Answers 3

Comments

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related