Updating without Looping - 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 2008 Forums

Transact-SQL (2008)

Updating without Looping

Author

Topic

Raskolnikov
Starting Member

8 Posts

Posted - 2012-04-05 : 16:07:18

My Table is similar to this:

Name: MyRelTable

Current Data

ID | ID2 | SEQ
a1 | b1 | 0
a2 | b2 | 0
a3 | b3 | 0

a4 | b4 | 0
a5 | b5 | 0
a6 | b6 | 0

a7 | b7 | 0
a8 | b8 | 0
a9 | b9 | 0

The results i am trying to accomplish

ID | ID2 | SEQ
a1 | b1 | 0
a1 | b2 | 1
a1 | b3 | 2

a2 | b4 | 0
a2 | b5 | 1
a2 | b6 | 2

a3 | b7 | 0
a3 | b8 | 1
a3 | b9 | 2

This is a relationship table. The short of it is, i am trying to take the data and group them into 3 by changing ID to the same value. Also then SEQ becomes 0,1,2 based on their order.

I am trying to understand how i can update the table in a certain way without really looping through the resultset as loops take up a lot of resources.

My thoughts are this may have to be a stored procedure. But to be honest, this goes beyond my experience level. There are some other factors involved, but this is the basics of it, and will try to build off of this.

Any help would be appreciated.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 16:20:33

[code]
UPDATE t
SET Seq=Rn-1
FROM (SELECT Seq,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID2) AS Rn
FROM table)t
[/code]

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

Raskolnikov
Starting Member

8 Posts

Posted - 2012-04-05 : 17:17:19

Thanks for the response. Two possible issues.

#1 I should have mentioned earlier that ID and ID2 are actually of uniqueidentifier type. I probly used bad examples. just wanted to simplify the table data.

#2 I need to update the ID column as well to reflect the same id in the groups of three

Again, thanks for the response. I am currently working with your suggestion to see if I can make it work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 18:51:08

couple of questions

1. how are uniqueidentifiers populated currently?
2. so you will do ID update first?

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

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-04-08 : 18:17:42

quote:
Originally posted by Raskolnikov

My Table is similar to this:

Name: MyRelTable

Current Data

ID | ID2 | SEQ
a1 | b1 | 0
a2 | b2 | 0
a3 | b3 | 0

a4 | b4 | 0
a5 | b5 | 0
a6 | b6 | 0

a7 | b7 | 0
a8 | b8 | 0
a9 | b9 | 0

The results i am trying to accomplish

ID | ID2 | SEQ
a1 | b1 | 0
a1 | b2 | 1
a1 | b3 | 2

a2 | b4 | 0
a2 | b5 | 1
a2 | b6 | 2

a3 | b7 | 0
a3 | b8 | 1
a3 | b9 | 2

This is a relationship table. The short of it is, i am trying to take the data and group them into 3 by changing ID to the same value. Also then SEQ becomes 0,1,2 based on their order.

I am trying to understand how i can update the table in a certain way without really looping through the resultset as loops take up a lot of resources.

My thoughts are this may have to be a stored procedure. But to be honest, this goes beyond my experience level. There are some other factors involved, but this is the basics of it, and will try to build off of this.

Any help would be appreciated.

Thanks

So you want to just throw away the values for A4 through A9 (regardless if they're GUID's or not)?

--Jeff Moden

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-09 : 04:40:04

1. Please post the ddl of the table.
2. Please tell us how the unique identifiers are populated currently.

Vinu Vijayan

Raskolnikov
Starting Member

8 Posts

Posted - 2012-04-09 : 12:38:25

Alright...

So the example i used was a really bad example i guess. I tried to simplify the table so that it would be easier to discuss. but in hindsight useing sequential numbers made it look like my ID's were also sequential which is not the case. I will try o explain further.

I do not know specifically how the unique id's are being generated originally (i believe they are generated at the application level). The ID's within this table are populated from the two different parent tables. again, this is a relationship table so these are just referring to data within two separate tables.

assume ID column could be any 23 character value.

assume ID2 could also be any 23 character value.

Here is an actual 6 record recordset from my db.

ID1 | ID2 | SEQ
C20111227122539010BFC37 | A20111227122539010BFC34 | 0
C20111227122540010BFC40 | A20111227122540010BFC3D | 0
C20111227122540010BFC49 | A20111227122540010BFC46 | 0
C20111227122540010BFC52 | A20111227122540010BFC4F | 0
C20111227122540010BFC5B | A20111227122540010BFC58 | 0
C20111227122540010BFC64 | A20111227122540010BFC61 | 0

"so you will do ID update first?"

yes after looking at this, it seems that I may have to change the ID first unless there is a way to do it simultaneously.

Basically i need it to look like this:

ID | ID2 | SEQ
C20111227122539010BFC37 | A20111227122539010BFC34 | 0
C20111227122539010BFC37 | A20111227122540010BFC3D | 1
C20111227122539010BFC37 | A20111227122540010BFC46 | 2

C20111227122540010BFC52 | A20111227122540010BFC4F | 0
C20111227122540010BFC52 | A20111227122540010BFC58 | 1
C20111227122540010BFC52 | A20111227122540010BFC61 | 2

the ID from Every third record is then propagated through the next two records. ID2 column is never effected.

So ID for records 2 and 3 = ID from record 1. ID from records 5 and 6 = ID from record 4. And so on....ID of records 8 and 9 = ID of record 7......

I also see that my original example did not express this concept very well either.

Then finally SEQ is populated 0,1,2 based on current recordset order. This i believe can be accomplished using the previously suggested ROW_NUMBER() command, but that will only work after i have the ID column modified if I am understanding Row_Number correct.

My apologies for not being as specific as i should have been.

Thank you for you help and patience.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-09 : 14:06:21

here is one way to get the SEQ numbers to look like you want. You can make this a derived table and update the real one by joining to it. Then i bet Visakh can get the ID1 column the way you want with a neat cross apply

Or he can just do it all in one shot.

SELECT 
 ID1,ID2
, CASE ((ROW_NUMBER() OVER(ORDER BY SEQ)) % 3) -1 WHEN  -1 THEN 2 ELSE ((ROW_NUMBER() OVER(ORDER BY SEQ)) % 3) -1  
	END AS SEQ	 
  FROM <YOUR TABLE HERE>

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 14:50:34

[code]
SELECT ID1,ID2,ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY ID2) -1 AS Seq
FROM
(
SELECT MIN(ID1) OVER (PARTITION BY Grp) AS ID1,ID2
FROM
(
SELECT ((ROW_NUMBER() OVER (ORDER BY ID)-1) / 3) AS Grp,ID1 ,ID2
FROM Table
)t
)r
[/code]

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-09 : 14:55:40

No cross apply?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 15:08:42

here's solution using cross apply to make Don happy


;With T
AS
(
SELECT (ROW_NUMBER() OVER (ORDER BY ID1) -1)/3 AS Grp,ID1,ID2
FROM table
)


SELECT MinID,t.ID1,t.ID2,ROW_NUMBER() OVER (PARTITION BY t1.MinID ORDER BY t.ID2)-1 AS Seq
FROM T t
CROSS APPLY(SELECT MIN(ID1) AS MInID
            FROM T
            WHERE Grp= t.Grp
            )t1

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

Raskolnikov
Starting Member

8 Posts

Posted - 2012-04-09 : 15:38:05

Thanks for the responses guys. I will give these a test run and see what happens.

Should i expect a difference in performance between a cross apply and the previous version?

Raskolnikov
Starting Member

8 Posts

Posted - 2012-04-09 : 16:59:44

Works great. Thanks again for the assistance.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-10 : 06:58:05

Thanks Visakh!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources