Updating multiple columns using dynamic sql

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)

Updating multiple columns using dynamic sql

Author

Topic

johnconstraint
Starting Member

23 Posts

Posted - 2012-10-22 : 12:47:58

Hi,

I have a requirement where I have to value something called a 'Global' row based on a hierarchy.

I have shown an example below, the first three rows are linked to the last 'Global' row by the value in the column 'Link_To_Global'. The values on the last row are valued based on 'Hierarchy'. For example Col1 is valued from row corresponding to ABC123 since the row with hierarchy 1 does not contain value for Col1.

The values on the last row are empty before my updation process begins.


Primary_Key    Col1    Col2    Col3    Col4    Hierarchy     Link_To_Global
---------------------------------------------------------------------------
ABC123         Y               1224               2          Glb1
DEF456                  Yes    2335               3          Glb1
GHY345                  No             ery        1          Glb1
Glb1           Y        No     1224    ery

The example I have shown reflects just one group of records, in the actual table there are about 2500 groups per day, starting something like Glb1 thru Glb2500. The record count with in each group varies from 2 to 10.

I have had success in updating the 'Global' row by using a static query with one column at a time. Since there are about 40 columns (the example shows only few columns, but the actual table has about 50 columns) that needs to be updated following the hierarchy logic, I am planning to use a dynamic stored procedure where in I will pass the column name and the stored procedure will build the sql for the column passed in as parameter and update the table, something like

EXEC usp_UpdateTable Col1
EXEC usp_UpdateTable Col2
EXEC usp_UpdateTable Col3
.
.
I am not really sure if using the stored procedure with dynamic sql is the right approach. If you guys have any better approach, please let me know. Any help is appreciated!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 13:26:25

so at the end of update how should above rows look like?

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

johnconstraint
Starting Member

23 Posts

Posted - 2012-10-22 : 13:55:29

The table would like this before update begins:


Primary_Key    Col1    Col2    Col3    Col4    Hierarchy     Link_To_Global
---------------------------------------------------------------------------
ABC123         Y               1224               2          Glb1
DEF456                  Yes    2335               3          Glb1
GHY345                  No             ery        1          Glb1
Glb1

After update it should look like this:

Primary_Key    Col1    Col2    Col3    Col4    Hierarchy     Link_To_Global
---------------------------------------------------------------------------
ABC123         Y               1224               2          Glb1
DEF456                  Yes    2335               3          Glb1
GHY345                  No             ery        1          Glb1
Glb1           Y        No     1224    ery

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-22 : 14:49:31

You can use recursive cte like shown below. The code parses, but I have not tested it, so before you run it anywhere it can do any harm, please run in a development environment.

;WITH cte AS
(
	SELECT Col1,Col2,Col3,Col4,Hierarchy,Link_To_Global,
		1 AS RN
	FROM Tbl 
	WHERE Hierarchy=1
	UNION ALL
	SELECT 
		COALESCE(NULLIF(c.Col1,''),t.Col1),
		COALESCE(NULLIF(c.Col2,''),t.Col2),
		COALESCE(NULLIF(c.Col3,''),t.Col3),
		COALESCE(NULLIF(c.Col4,''),t.Col4),
		t.Hierarchy,
		t.Link_To_Global,
		c.RN+1
	FROM
		Tbl t
		INNER JOIN cte c ON
			c.Link_to_Global = t.Link_To_Global
			AND t.Hierarchy = c.Hierarchy+1
)
UPDATE t SET
	t.Col1 = c.Col1,
	t.Col2 = c.Col2,
	t.Col3 = c.Col3,
	t.Col4 = c.Col4
FROM
	Tbl t
	CROSS APPLY
	(
		SELECT TOP 1 c.Col1,c.Col2,c.Col3,c.Col4
		FROM cte c
		WHERE c.Link_to_Global = t.PrimaryKey
		ORDER BY RN DESC
	) c;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 14:53:36

or do it as a set of individual updates




UPDATE t
SET t.Col1= t1.Col1
FROM Table t
INNER JOIN
(SELECT ROW_NUMBER() OVER (PARTITION BY Link_To_Global ORDER BY Hierarchy ) AS Seq,
Link_To_Global,Col1
FROM Table
WHERE  Link_To_Global >''
AND Col1 > '') t1
ON t1.Link_To_Global  = t.Primary_Key
AND t1.Seq=1
WHERE COALESCE(t.Link_To_Global ,'')=''


UPDATE t
SET t.Col2= t1.Col2
FROM Table t
INNER JOIN
(SELECT ROW_NUMBER() OVER (PARTITION BY Link_To_Global ORDER BY Hierarchy ) AS Seq,
Link_To_Global,Col2
FROM Table
WHERE  Link_To_Global >''
AND Col2 > '') t1
ON t1.Link_To_Global  = t.Primary_Key
AND t1.Seq=1
WHERE COALESCE(t.Link_To_Global ,'')=''


UPDATE t
SET t.Col3= t1.Col3
FROM Table t
INNER JOIN
(SELECT ROW_NUMBER() OVER (PARTITION BY Link_To_Global ORDER BY Hierarchy ) AS Seq,
Link_To_Global,Col3
FROM Table
WHERE  Link_To_Global >''
AND Col3 > '') t1
ON t1.Link_To_Global  = t.Primary_Key
AND t1.Seq=1
WHERE COALESCE(t.Link_To_Global ,'')=''

UPDATE t
SET t.Col4= t1.Col4
FROM Table t
INNER JOIN
(SELECT ROW_NUMBER() OVER (PARTITION BY Link_To_Global ORDER BY Hierarchy ) AS Seq,
Link_To_Global,Col4
FROM Table
WHERE  Link_To_Global >''
AND Col4 > '') t1
ON t1.Link_To_Global  = t.Primary_Key
AND t1.Seq=1
WHERE COALESCE(t.Link_To_Global ,'')=''

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

johnconstraint
Starting Member

23 Posts

Posted - 2012-10-22 : 15:07:49

sunitabeck and visakh16 - Thanks to both of you. Infact the approach I am following right is in similar lines to what visakh16 suggested, having separate update statements for each one of the columns. The only problem is that I have 40 columns to update, the logic seemed to be redundant as I am repeating the same logic for about 40 times with different column names.
I going to try with what sunitabeck suggested and will post my results.
Once again, thanks for your suggestions!

johnconstraint
Starting Member

23 Posts

Posted - 2012-10-25 : 16:07:40

Okay, so I tested both the approaches. The recursive CTE is taking more time than the individual update statements. So I am going to stick to individual update statments. Thanks to both of you for your valuable suggestions!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 16:14:21

welcome and thanks for posting test result

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources