UPDATE using local variable, subquery

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 2000 Forums

SQL Server Development (2000)

UPDATE using local variable, subquery

Author

Topic

ollydsouza
Starting Member

3 Posts

Posted - 2012-02-20 : 14:10:33

Hi Im trying to UPDATE a #temp table column with a @local_variable that needs to be initialized in the the same statement using COALESCE(), something like:

so COALESCE() will concatenate multiple row vals into the local var

UPDATE #temp
SET col5 = @local_var
FROM #temp, (Select @local_var = (COALESCE(@local_var + ', ', '') + COL3
FROM tab1, tab2 WHERE tab1.pk = tab2.pk) as inline_tab
AND #temp.id = inline_tab.id

Not able to put this together successfully...anyone to help?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 14:48:43

you cant do it inline.what you need is a table valued udf i guess


CREATE FUNCTION GetDetails
(@ID int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @local_var varchar(8000)

Select @local_var = COALESCE(@local_var + ', ', '') + COL3
FROM tab1, tab2 
WHERE tab1.pk = tab2.pk
AND tab1.id = @ID
RETURN @Loacl_var
END

then use it like

UPDATE #temp
SET col5 = dbo.GetDetails(id)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ollydsouza
Starting Member

3 Posts

Posted - 2012-02-20 : 15:52:21

I guess that would work....
Alternatively, Im looping through the #temp table in a CURSOR, initializing the @local_var with the COALESCE and then UPDATEing each row. However, I dont know which method is taking costing more overheads.

quote:
Originally posted by visakh16

you cant do it inline.what you need is a table valued udf i guess

CREATE FUNCTION GetDetails
(@ID int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @local_var varchar(8000)

Select @local_var = COALESCE(@local_var + ', ', '') + COL3
FROM tab1, tab2
WHERE tab1.pk = tab2.pk
AND tab1.id = @ID
RETURN @Loacl_var
END

then use it like

UPDATE #temp
SET col5 = dbo.GetDetails(id)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 15:59:13

quote:
Originally posted by ollydsouza

I guess that would work....
Alternatively, Im looping through the #temp table in a CURSOR, initializing the @local_var with the COALESCE and then UPDATEing each row. However, I dont know which method is taking costing more overheads.

quote:
Originally posted by visakh16

you cant do it inline.what you need is a table valued udf i guess

CREATE FUNCTION GetDetails
(@ID int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @local_var varchar(8000)

Select @local_var = COALESCE(@local_var + ', ', '') + COL3
FROM tab1, tab2
WHERE tab1.pk = tab2.pk
AND tab1.id = @ID
RETURN @Loacl_var
END

then use it like

UPDATE #temp
SET col5 = dbo.GetDetails(id)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

not sure on that
but cursor iterates it row by row only

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ollydsouza
Starting Member

3 Posts

Posted - 2012-02-23 : 13:15:45

I implemented the function, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 14:58:55

great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources