CODEWITHSUNDEEP
asp.netsql
user2427023
user2427023

Reputation: 107

Update multiple rows from select statement

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

Upvotes: 0

Views: 107

Answers (3)

Kaf
Kaf

Reputation: 33819

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'

Upvotes: 0

Gary W
Gary W

Reputation: 1904

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

Upvotes: 1

Jitendra Pancholi
Jitendra Pancholi

Reputation: 7562

Is this you want?

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

Upvotes: 0

Related Questions