37 updates in one - 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

General SQL Server Forums

New to SQL Server Programming

37 updates in one

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-10 : 14:57:02

okay, so i have about 37 different updates i need to do to a table that is rather large (71million) and has no indexes. i know it's gunna table scan, and honestly, i'm not really worried about that. my question is, is there a way up squeeze all of these updates into one?

here is what i was going to do, but each one will take about an hour to run... (here are 5 of the 37 updates, but they are all basically the same concept)


update t1 set books_music='' from mailorder t1 where books_music is null 
update t1 set Car_Buff='' from mailorder t1 where Car_Buff is null 
update t1 set Childrens_Items_Buyers='' from mailorder t1 where Childrens_Items_Buyers is null 
update t1 set Computer='' from mailorder t1 where Computer is null 
update t1 set Crafts_Sewing='' from mailorder t1 where Crafts_Sewing is null

in FoxSlow (foxpro) i could just do something like this:


do while !eof()
  replace books_music with '' for books_music=null
  replace car_buff with '' for car_buff=null
  skip
enddo

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-10 : 15:00:47

well you could do a series of case when's in your update....


update t1 
set    books_music = case when books_music is null then '' else books_music end, 
       Car_Buff = case when Car_Buff is null then '' else Car_Buff end, 
       ...
from   mailorder t1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-10 : 15:03:11

Can't you use COALESCE function?

Untested:
update mailorder
set crafts_sewing = coalesce(crafts_sewing, ''), car_buff = coalesce(car_buff, ''), ...

Tara Kizer
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-10 : 15:05:32

well i assumed that there wouldn't be all nulls in his wheres

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-10 : 15:07:13

My Goodness .... another example of a really bad design causing massive maintenance issues ....

Please consider, if you can, restructuring your design so that it is normalized .. then things like this become simple and efficient 1-line update statements ... or, you probably won't even need mass updates like this, since you won't have nulls and empty strings all over the place.

http://www.datamodel.org/NormalizationRules.html

- Jeff
http://weblogs.sqlteam.com/JeffS

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-10 : 15:08:57

Spirit1 your assuming correct, and your solution looks like the way to go. thanks!

Kristen
Test

22859 Posts

Posted - 2007-05-10 : 15:09:44

"a table that is rather large ... and has no indexes"


update t1 set books_music='' from mailorder t1 where books_music is null

Well a decent Index would help with that. but so, presumably, would a decent Default constraint. or if you can't do that then a decent Trigger

Kristen

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-10 : 15:24:25

Spirit1, i tried:


update t1 set 	

	books_music='' case when book_music is null then '' else books_music='Y' end,
	Car_Buff='' case when Car_Buff is null then '' else Car_Buff='Y' end,
	Childrens_Items_Buyers='' case when Childrens_Items_Buyers is null then '' else Childrens_Items_Buyers='Y' end,
	Computer='' case when Computer is null then '' else Computer='Y' end,
	apparel='Y' case when apparel='yes' else apparel end

from mailorder t1

i got an incorect syntex line 3.

ideas?

quote:

another example of a really bad design causing massive maintenance issues

this is actully a custom thing from a client that is a crybaby an needs the data their way. the way i have this table set up (the original one) it's usually fairly effecent. my table has ONE column, with all these values in it, the client wanted each value to be a column with a "YES" or NULL in the field. then they changed it a month later to "Y" or nothing. so i'm adapting what i've already done to suite their needs.

Kristen
Test

22859 Posts

Posted - 2007-05-10 : 15:27:48

[code]
books_music='' case when book_music is null then '' else books_music='Y' end,
[/code]
should be something like
[code]
books_music = case when book_music is null then '' else 'Y' end,
[/code]
Kristen

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-10 : 15:39:55

Perfect! thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources