Multiple Column Update - 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 2000 Forums

Transact-SQL (2000)

Multiple Column Update

Author

Topic

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-04-15 : 10:14:41

I was wondering if there is a way to update all collumns in a table where a certain condition exists.

I.E.
Update northwind
Set columns = ''
where columns is null

I hope that this is available, because some tables have over 100 columns in them.

THanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-15 : 10:18:58

no ... you have to type ... or write a program to type for you.

Jay White
{0}

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-15 : 10:28:18

I see Brett fumbling in his back-pocket for one of those nifty syscolumn scripts...here it comes!

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-04-15 : 10:33:55

Seems to me ive seen something like it before, I just cant believe it cant be done.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 10:35:02

This might Help. Take a look and let me know.

Thanks



USE NorthWind

DECLARE @TBName sysname, @TBName2 sysname

SELECT @TBName = 'Orders', @TBName2 = 'Orders2'

SELECT SQL FROM (

SELECT 'UPDATE ' + @TBName + ' SET ' + COLUMN_NAME + ' = ISNULL('+COLUMN_NAME+','
	+'''' + '''' + ')' As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = @TBName
   AND ORDINAL_POSITION = 1
UNION ALL
SELECT '     , ' + COLUMN_NAME + ' = ISNULL('+COLUMN_NAME+','
	+'''' + '''' + ')' As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = @TBName
   AND ORDINAL_POSITION <> 1
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 10:37:45

God, am I THAT predicatable....

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-15 : 13:31:30

That was a compliment...mebbe you can get the title of Posting Script Yak Master - hey, if we can have a Yak Date-Master General, then why not?

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 13:42:34

Well, thank you.

BenSwitzer: Did it help?

Brett

8-)

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-04-15 : 14:14:41

Yep it created the sql statement to update all the columns. Im surprised that there isnt some sort of colums keyword in sql to make it easier

thanks
Ben Switzer

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 14:25:50

Huh? Columns keyword? To do what?

Hey mohdowais: Have you seen:

http://www.dubaitourism.co.ae/www/main/content.asp

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-15 : 14:55:50

brett: yup, i have seen it, and i keep wishing our tourism site was better (at least the picture gallery!). Dubai is really beautiful and it certainly deserves better than this!! This one's a little better - http://www.utc-dubai.com/picture%20gallery.htm . So, you've been doing some homework on Dubai, eh?

Ben: Sooo sorry to hijack your thread, but just get carried away

. I was thinking about your "columns" keyword suggestions, i gues it will make it even easier for some people to screw up things

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 15:01:41

mohdowais,

Yeah looks beautiful....diggin the belly dancing shots...

Ben: Is this what your looking for?

UPDATE TABLE ALL COLUMNS = ''

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources