UPDATE BLANK ROWS - 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 BLANK ROWS

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2010-08-18 : 10:19:00

----I have a table like this. There are rowids in the table. Row 2 to 5 should be updated with FRUIT which is the category above them
----and when category changes the next blank rows should be populate with that category. So Row 7 to 10 should have WEIGHT.

We can use WHERE LEN(CATEGORY)>1 THOUGH.

----THIS IS WHAT THE TABLE IS

DECLARE @temp TABLE
(ROWID INT IDENTITY(1,1), CATEGORY VARCHAR(20), SALES VARCHAR(20))
INSERT @temp
SELECT 'FRUIT', '' UNION ALL
SELECT '', 'ABOVE 10,000' UNION ALL
SELECT '', 'ABOVE 20,000' UNION ALL
SELECT '', 'ABOVE 30,000' UNION ALL
SELECT '', 'ABOVE 40,000' UNION ALL
SELECT 'WEIGHT', '' UNION ALL
SELECT '', '10' UNION ALL
SELECT '', '20' UNION ALL
SELECT '', '30' UNION ALL
SELECT '', '40' UNION ALL
SELECT '', '50' UNION ALL
SELECT '', '60' UNION ALL
SELECT 'COLOR', '' UNION ALL
SELECT '', 'BLACK' UNION ALL
SELECT '', 'RED' UNION ALL
SELECT '', 'GREY' UNION ALL
SELECT '', 'GREEN' UNION ALL
SELECT 'PERCENTAGE', '' UNION ALL
SELECT '', '>10%' UNION ALL
SELECT '', '>20%' UNION ALL
SELECT '', '>30%'
SELECT * from @temp

-- --THIS IS WHAT TABLE SHOULD LOOK LIKE AFTER UPDATE. NO HARDCODING. THE CATEGORIES CAN CHANGE SO CAN ROWS WITH EACH CATEGORY

DECLARE @temp1 TABLE
(ROWID INT IDENTITY(1,1), CATEGORY VARCHAR(20), SALES VARCHAR(20))
INSERT @temp1
SELECT 'FRUIT', '' UNION ALL
SELECT 'FRUIT', 'ABOVE 10,000' UNION ALL
SELECT 'FRUIT', 'ABOVE 20,000' UNION ALL
SELECT 'FRUIT', 'ABOVE 30,000' UNION ALL
SELECT 'FRUIT', 'ABOVE 40,000' UNION ALL
SELECT 'WEIGHT', '' UNION ALL
SELECT 'WEIGHT', '10' UNION ALL
SELECT 'WEIGHT', '20' UNION ALL
SELECT 'WEIGHT', '30' UNION ALL
SELECT 'WEIGHT', '40' UNION ALL
SELECT 'WEIGHT', '50' UNION ALL
SELECT 'WEIGHT', '60' UNION ALL
SELECT 'COLOR', '' UNION ALL
SELECT 'COLOR', 'BLACK' UNION ALL
SELECT 'COLOR', 'RED' UNION ALL
SELECT 'COLOR', 'GREY' UNION ALL
SELECT 'COLOR', 'GREEN' UNION ALL
SELECT 'PERCENTAGE', '' UNION ALL
SELECT 'PERCENTAGE', '>10%' UNION ALL
SELECT 'PERCENTAGE', '>20%' UNION ALL
SELECT 'PERCENTAGE', '>30%'
SELECT * from @temp1

-----------------------------------------------------------------------------------------------
Ashley Rhodes

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-18 : 10:41:11

declare @Category varchar(20)

UPDATE @temp
SET @Category = Category = CASE WHEN Category = '' then @category else category end

Jim

Check out Visakh's quirky update

Everyday I learn something that somebody else already knew

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2010-08-18 : 10:47:10

Well that does work but I don't understand the logic. Can you please explain how this thing is working

-----------------------------------------------------------------------------------------------
Ashley Rhodes

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 10:47:38

There's something called a quirky update you can use. I put it in a transaction so it can be rolled back for testing. Try this


DECLARE @temp TABLE
(ROWID INT IDENTITY(1,1), CATEGORY VARCHAR(20), SALES VARCHAR(20))
INSERT @temp 
SELECT 'FRUIT', '' UNION ALL
SELECT '', 'ABOVE 10,000' UNION ALL
SELECT '', 'ABOVE 20,000' UNION ALL
SELECT '', 'ABOVE 30,000' UNION ALL
SELECT '', 'ABOVE 40,000' UNION ALL
SELECT 'WEIGHT', '' UNION ALL
SELECT '', '10' UNION ALL
SELECT '', '20' UNION ALL
SELECT '', '30' UNION ALL
SELECT '', '40' UNION ALL
SELECT '', '50' UNION ALL
SELECT '', '60' UNION ALL
SELECT 'COLOR', '' UNION ALL
SELECT '', 'BLACK' UNION ALL
SELECT '', 'RED' UNION ALL
SELECT '', 'GREY' UNION ALL
SELECT '', 'GREEN' UNION ALL
SELECT 'PERCENTAGE', '' UNION ALL
SELECT '', '>10%' UNION ALL
SELECT '', '>20%' UNION ALL
SELECT '', '>30%' 
SELECT * from @temp

declare @category varchar(20)

begin tran

update @temp
        set @Category = Category =  
                        case when @Category is null then
                                category
                             when Category is null or Category = '' then
								@Category
						else category 
                        end
OPTION (MAXDOP 1)  
 
 select * from @temp

rollback tran

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-18 : 11:04:53

quote:

Well that does work but I don't understand the logic. Can you please explain how this thing is working

-----------------------------------------------------------------------------------------------
Ashley Rhodes

Visakh's
http://visakhm.blogspot.com/search?q=quirky

and Madhi's

http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-18 : 11:10:40

....Ahem...

The Physical Order of Data in a Database has no Meaning

Unless you have something to realte the rows together..ANY process you build will be...ummm...suspect at best

EDIT: Man we are getting a lot of these lately

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources