please help! Cursor or loop?

Question

please help! Cursor or loop?

kenny_scriber

SSC Rookie

Points: 48
More actions
July 7, 2009 at 12:18 pm

#73933

Hi Guys, I am trying to write an update query that will update null rows with the last non null row. My data looks like this:
PK ID Field1
-- ------ ------
1 Name1Data1
2 Data2
3 Data3
4 data4
5 data5
6 data6
7 Data7
8
9 Name2data1
10 data2
11 data3
12 data4
13 data5
14 data6
15 data7
16 data8
17 data9
18
19 Name3data1
20 data2
21
22 Name4data1
23 data2
24 data3
25 data4
26 data5
I need the final data to look like this:
PK ID Field1
-- ------ ------
1 Name1 Data1
2 Name1 Data2
3 Name1 Data3
4 Name1 data4
5 Name1 data5
6 Name1 data6
7 Name1 Data7
8
9 Name2 data1
10 Name2 data2
11 Name2 data3
12 Name2 data4
13 Name2 data5
14 Name2 data6
15 Name2 data7
16 Name2 data8
17 Name2 data9
18
etc....
Any help would be greatly appreciated

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

kenny_scriber (7/7/2009)
Hi Guys, I am trying to write an update query that will update null rows with the last non null row. My data looks like this:
PK ID Field1
-- ------ ------
1 Name1Data1
2 Data2
3 Data3
4 data4
5 data5
6 data6
7 Data7
8
9 Name2data1
10 data2
11 data3
12 data4
13 data5
14 data6
15 data7
16 data8
17 data9
18
19 Name3data1
20 data2
21
22 Name4data1
23 data2
24 data3
25 data4
26 data5
I need the final data to look like this:
PK ID Field1
-- ------ ------
1 Name1 Data1
2 Name1 Data2
3 Name1 Data3
4 Name1 data4
5 Name1 data5
6 Name1 data6
7 Name1 Data7
8
9 Name2 data1
10 Name2 data2
11 Name2 data3
12 Name2 data4
13 Name2 data5
14 Name2 data6
15 Name2 data7
16 Name2 data8
17 Name2 data9
18
etc....
Any help would be greatly appreciated

To answer your question... neither.

To get better, faster help, I suggest you supply table structures / sample data so that folks can cut/paste/run to have the sample loaded. See the link in my signature for how to do this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I agree with Wayne on both points. I have the same article referenced below in my signature block as well. It is the first one regarding asking for assistance.

kenny_scriber SSC Rookie Points: 48 More actions · Answer 3

My sincere apologies! I'll try again:

/* If the test table already exists, drop it */

IF OBJECT_ID('TempDB..#tblTest','U') IS NOT NULL

DROP TABLE #tblTest

/* Create the test table */

CREATE TABLE #tblTest(

[ID] [int] IDENTITY(1,1) NOT NULL,

[Field1] [nvarchar](255) NULL,

[Field2] [nvarchar](255) NULL)

SET IDENTITY_INSERT #tblTest ON

/*Insert the test data into the test table*/

INSERT INTO #tblTest

(ID, Field1, Field2)

SELECT '1','Name1','Data1' UNION ALL

SELECT '2', NULL, 'Data2' UNION ALL

SELECT '3', NULL, 'Data3' UNION ALL

SELECT '4', NULL, 'Data4' UNION ALL

SELECT '5', NULL, 'Data5' UNION ALL

SELECT '6', NULL, 'Data6' UNION ALL

SELECT '7', NULL, 'Data7' UNION ALL

SELECT '8', NULL, 'NULL' UNION ALL

SELECT '9','Name2','Data1' UNION ALL

SELECT '10', NULL, 'Data2' UNION ALL

SELECT '11', NULL, 'Data3' UNION ALL

SELECT '12', NULL, 'Data4' UNION ALL

SELECT '13', NULL, 'NULL' UNION ALL

SELECT '14','Name3','Data1' UNION ALL

SELECT '15', NULL, 'Data2' UNION ALL

SELECT '16', NULL, 'Data3' UNION ALL

SELECT '17', NULL, 'Data4'

SET IDENTITY_INSERT #tblTest OFF

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

What do you want to occur when both Field1 and Field2 are null? Leave them null?

kenny_scriber SSC Rookie Points: 48 More actions · Answer 5

kenny_scriber

SSC Rookie

Points: 48

July 7, 2009 at 3:04 pm

#1021345

Yes, that would be fine, thank you in advance.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 6

There are much nicer solutions (if you have SQL 2005 or later), but here is one way to do it:DECLARE @test-2 TABLE (

[ID] [int] NOT NULL,

[Field1] [nvarchar](255) NULL,

[Field2] [nvarchar](255) NULL)

INSERT @test-2

SELECT 1,'Name1','Data1' UNION ALL

SELECT 2, NULL, 'Data2' UNION ALL

SELECT 3, NULL, 'Data3' UNION ALL

SELECT 4, NULL, 'Data4' UNION ALL

SELECT 5, NULL, 'Data5' UNION ALL

SELECT 6, NULL, 'Data6' UNION ALL

SELECT 7, NULL, 'Data7' UNION ALL

SELECT 8, NULL, 'NULL' UNION ALL

SELECT 9,'Name2','Data1' UNION ALL

SELECT 10, NULL, 'Data2' UNION ALL

SELECT 11, NULL, 'Data3' UNION ALL

SELECT 12, NULL, 'Data4' UNION ALL

SELECT 13, NULL, 'NULL' UNION ALL

SELECT 14,'Name3','Data1' UNION ALL

SELECT 15, NULL, 'Data2' UNION ALL

SELECT 16, NULL, 'Data3' UNION ALL

SELECT 17, NULL, 'Data4'

DECLARE @max-2 INT

SELECT @max-2 = MAX(ID) + 1 FROM @test-2 WHERE Field2 'NULL'

UPDATE

T

SET

Field1 = D.Field1

FROM

@test-2 AS T

INNER JOIN

(

SELECT

A.ID,

A.Field1,

COALESCE(MIN(B.ID), @max-2) AS BID

FROM

(

SELECT ID, Field1

FROM @test-2

WHERE Field1 IS NOT NULL

) AS A

LEFT OUTER JOIN

(

SELECT ID, Field1

FROM @test-2

WHERE Field1 IS NOT NULL

) AS B

ON A.ID D.ID

AND T.ID < D.BID

AND T.Field2 'NULL'

SELECT *

FROM @test-2 PS - Is there a way to get the CODE tags to actualy format the code as entered?

WayneS SSC Guru Points: 95461 More actions · Answer 7

Here you go.

Note that you don't actually have a NULL value in Field2; instead you have the string "NULL'. The code handles this.

-- with this clustered index, the following update statement will update in that order

ALTER TABLE #tblTest ADD CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED (ID)

-- need a variable to hold the last populated value, and to use for updating the null values

declare @Field1 nvarchar(255)

-- do the update

update #tblTest

set @Field1 = IsNull(Field1, @Field1), --<< Get the value to use for the update for following fields

Field1 = case when Field1 is null and IsNull(Field2, 'NULL') = 'NULL' then null else @Field1 end

from #tblTest with (TABLOCKX, INDEX(0)) -- lock the table while updating, use the clustered index

-- show the results

select * from #tblTest

Edit: added update hints

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 8

WayneS (7/7/2009)
Here you go.
Note that you don't actually have a NULL value in Field2; instead you have the string "NULL'. The code handles this.
-- with this clustered index, the following update statement will update in that order
ALTER TABLE #tblTest ADD CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED (ID)
-- need a variable to hold the last populated value, and to use for updating the null values
declare @Field1 nvarchar(255)
-- do the update
update #tblTest
set @Field1 = IsNull(Field1, @Field1), --<< Get the value to use for the update for following fields
Field1 = case when Field1 is null and IsNull(Field2, 'NULL') = 'NULL' then null else @Field1 end
-- show the results
select * from #tblTest

This will probably work, but you cannot guarantee that it'll work based on the PK order.

WayneS SSC Guru Points: 95461 More actions · Answer 9

Lamprey13 (7/7/2009)
This will probably work, but you cannot guarantee that it'll work based on the PK order.

Thanks for pointing that out... I've edited the post to include the necessary hints to ensure it will work in PK order.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kenny_scriber SSC Rookie Points: 48 More actions · Answer 10

Thank you to everyone that responded! I will post the final code once i implement it.

WayneS SSC Guru Points: 95461 More actions · Answer 11

kenny_scriber (7/7/2009)
Thank you to everyone that responded! I will post the final code once i implement it.

Great! It's always nice to see the solution that you end up implementing... it just might contain details that another person could find useful for a problem that they are having.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 12

WayneS (7/7/2009)
Lamprey13 (7/7/2009)
This will probably work, but you cannot guarantee that it'll work based on the PK order.
Thanks for pointing that out... I've edited the post to include the necessary hints to ensure it will work in PK order.

Just to be sure... it's not the PK order that will be obeyed. It's the clustered index order that will be obeyed and actually, the INDEX(0) hint isn't ever necessary... I use it just to keep people from installing handrails on my hiney and it actually causes the code to run quite a bit slower. Update always update in clustered index order when certain rules are met. TABLOCKX ensures exclusive use and, on a single table, most other rules are automatically met. The addition of the MAXDOP 1 option will guarantee that no parallelism kicks in. Partitioned tables have a slightly different set of rules.

I've stopped using the INDEX(0) hint to make everyone happy because it makes the code take 2-3 times as long and is, as I said, totally unnecessary. Gor those that give me a hard time about the method, I just tell them "Show me just one example that follows the rules and doesn't work." No one has been able to do so. I won't say "yet" because unless they change the fundamental way that UPDATE has worked since the birth of Sybase, it will always work correctly.

Man... I've gotta finish the rewrite of that article on the subject... there's just a lot to cover. Thought I was almost done and found some other myths about it not working that need to be dispelled.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95461 More actions · Answer 13

Jeff Moden (7/8/2009)
Just to be sure... it's not the PK order that will be obeyed. It's the clustered index order that will be obeyed and actually, the INDEX(0) hint isn't ever necessary

I did indicate that in the remarks in my code; I just erroneously put "PK" in the reply to the post.

Update always update in clustered index order when certain rules are met. TABLOCKX ensures exclusive use and, on a single table, most other rules are automatically met. The addition of the MAXDOP 1 option will guarantee that no parallelism kicks in. Partitioned tables have a slightly different set of rules.

So, just to make sure I have it correct, I should have used from #tblTest with (TABLOCKX) OPTION (MAXDOP 1) Correct?

Man... I've gotta finish the rewrite of that article on the subject... there's just a lot to cover. Thought I was almost done and found some other myths about it not working that need to be dispelled.

I'm waiting for it... in fact, if I can help somehow, let me know.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 14

WayneS (7/8/2009)[hrSo, just to make sure I have it correct, I should have used from #tblTest with (TABLOCKX) OPTION (MAXDOP 1) Correct?

Correct. Man... sorry... I got way behind on the forum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)