Update with multiple values - 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)

Update with multiple values

Author

Topic

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-04-22 : 22:10:26

I have a table say #temp1 with coulmn Id and Description
and I do have another table #temp2 with column id and Projectdescription
but in #temp2 there could be more then one value against one id
like the data in #temp2 is look like

id Projectdescription
1 Computer project
1 update in computer project
1 another update in computer
2 Physics project
2 another update

but #temp1 has only one accurance of id
and data initially looks like
id Description
1 NUll
2 NUll
and I would like to update this table description from #temp2 Projectdescription column so that
data in #temp1 table look like after update

id Description
1 Computer project,update in computer project, another update in computer

2 Physics project, another update

I mean I would like to have concatination form of Projectdescription in description column against specific ID
I can achieve this by using UDF but i dont want to use that I just want to do it by update statement not even by using cursor

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-04-22 : 22:35:28

uSE A UDF. It's the correct solution for the scenerio you described.

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-04-22 : 22:41:30

It took lot of time if your table do have lot of records so thats why I m not going with this option
thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-04-22 : 22:54:16

Here's some samples, I think you'll find the UDF provides your best performance, but try a few scenerios.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-04-22 : 23:08:18

I was thinking that It might work but it does not
Update #temp1
set Description=Description+Projectdescription
from #temp2
where #temp2.id=#temp1.id

I dont know why is not working if i store this in variable it did work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-23 : 00:13:40

[code]UPDATE t
SET t.Description=LEFT(pl.projectlist,LEN(pl.projectlist)-1)
FROM #temp1 t
CROSS APPLY (SELECT ProjectDescription + ',' AS [text()]
FROM #temp2
WHERE id=t.id
FOR XML PATH(''))pl(projectlist)[/code]

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-04-23 : 00:58:22

Excellent stuff
But can we do it in Sql serevr 2000
really apprecaited if it could be possible

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-23 : 03:27:06

quote:
Originally posted by QAZAFI

Excellent stuff
But can we do it in Sql serevr 2000
really apprecaited if it could be possible

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Madhivanan

Failing to plan is Planning to fail

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-04-23 : 17:03:43

Thanks for the reply and sorry to say I know all this kind of solution But I would like to have similar kind of solution as visakh16 did
Just would like to do in one update statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources