Using UPDATE and CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using UPDATE and CTE

Author

Topic

meatsqlteam
Starting Member

2 Posts

Posted - 2008-06-27 : 07:00:39

Hello,

Can an UPDATE clause be used with a CTE?

I mean can the UPDATE be used to recursively update the records in a table??

I dont want to use any kind of loop and want to know if we can UPDATE the records in a SET based manner with the difference that different records would get updated with different values?

Regards,
meatsqlteam

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-27 : 07:06:37

Yes we can update the table recursively without using any kind of loop. See the below sample update query.

update @results
set onTargetShotCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from tempEvents
where (event = 9 or event = 10 or event=36 or event=38)
and outcome in (3,5) -- blocked on target, saved on target, goal
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

I am updating the @Results Table based on the MatchID and PlayerID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 07:25:49

quote:
Originally posted by ganeshkumar08

Yes we can update the table recursively without using any kind of loop. See the below sample update query.

update @results
set onTargetShotCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from tempEvents
where (event = 9 or event = 10 or event=36 or event=38)
and outcome in (3,5) -- blocked on target, saved on target, goal
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

I am updating the @Results Table based on the MatchID and PlayerID

where are you doing recursive update in this code?

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-27 : 07:39:59

in @Results Table first i insert the MatchID and PlayerID.
After inserting i am updating onTargetShotCount to each playerid for a match.

I thought this is recursive update..

if this is not recursive update query means i am sorry.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 07:44:15

quote:
Originally posted by ganeshkumar08

in @Results Table first i insert the MatchID and PlayerID.
After inserting i am updating onTargetShotCount to each playerid for a match.

I thought this is recursive update..

if this is not recursive update query means i am sorry.

See example of recursive query here:-

http://www.mssqltips.com/tip.asp?tip=1520

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 07:45:33

quote:
Originally posted by meatsqlteam

Hello,

Can an UPDATE clause be used with a CTE?

I mean can the UPDATE be used to recursively update the records in a table??

I dont want to use any kind of loop and want to know if we can UPDATE the records in a SET based manner with the difference that different records would get updated with different values?

Regards,
meatsqlteam

yup this can be done. Can you elaborate on what your requirement is?

meatsqlteam
Starting Member

2 Posts

Posted - 2008-06-27 : 08:24:16

Following is the scenario,

1. I have a table of Mentors (Mentor Name, Mentor ID)
2. I have a table of Mentees (Mentee Name, Mentor ID)
3. I need to update the MentorId column in Mentee table. The condition being that one Mentor cannot be assigned to more than one mentee.
4. I can easily achieve this by writing a While Loop.

BUT, I want to know if this can be done in a SET based operation so that I aviod any looping.

I am providing scripts below to create the tables.

CREATE TABLE Mentor(MentorID int, MentorName varchar(20))

DECLARE @i int
SET @i = 1
WHILE (@i < 6)
BEGIN
INSERT INTO Mentor (MentorID, MentorName) VALUES(@i,'M'+CAST(@i as varchar))
SET @i = @i + 1
END

CREATE TABLE Mentee(ResourceName varchar(20), MentorID INT)

DECLARE @i int
SET @i = 0
WHILE (@i < 10)
BEGIN
INSERT INTO Mentee (ResourceName) VALUES('Jay' + CAST(@i as varchar))
SET @i = @i + 1
END

Do kindly let me know your views ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 09:54:51

[code];
With Mentee_CTE (RowNo,MenteeName,MentorID) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY MenteeName),MenteeName,MentorID
FROM Mentees)

UPDATE m
SET m.MentorID=t.MentorID
FROM Mentee_CTE m
LEFT JOIN (SELECT ROW_NUMBER() OVER(ORDER BY MenteeName) AS RowNo,MentorName,MentorID
FROM Mentors) t
ON t.RowNo=m.RowNo[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources