How to update, in a table, all the columns with a name that begins with the same string (same value for all)

Question

Post reply

How to update, in a table, all the columns with a name that begins with the same string (same value for all)

nicolas.pages

SSC-Addicted

Points: 430
More actions
April 29, 2008 at 3:26 am

#72021

Hi,
I have a table with around 60 columns. 20 of them have a name that begin with "aa_". I want to set the same value to all of them. Is there any quick and easy way to do that?
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Hi,

You could use Dynamic SQL to do it. Something like the following

DECLARE @tableName VARCHAR(30)

DECLARE @newValue VARCHAR(30)

DECLARE @sql VARCHAR(2000)

/* Set Values */

SET @tableName = 'myTable'

SET @newValue = 'changeToThis'

/* Build Update statement */

SELECT @sql = 'UPDATE ' + @tableName + ' SET '

SELECT @sql = @sql + ' sc.name + ' = ' + @newValue + ', '

FROM sysobjects so

INNER JOIN syscolumns sc

ON so.id = sc.id

WHERE so.name = @tableName

AND sc.name LIKE 'aa[_]%'

/* Trim final comma */

SELECT @sql = LEFT(@sql, LEN(@sql) -1)

/* Review SQL Statement first */

PRINT @sql

/* Uncomment to execute */

-- EXEC (@sql)

HTH,

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

nicolas.pages SSC-Addicted Points: 430 More actions · Answer 2

Hi and thanks for your answer! It helps a lot.

However.. : ) I understand this gives me all the column names I need :

SELECT sc.name

FROM sysobjects so

INNER JOIN syscolumns sc

ON so.id = sc.id

WHERE so.name = 'MyTable'

AND sc.name LIKE 'aa_%'

But I can't manage to get them into a big update statement.. For now my best idea would consist in puting the result in a temp table and go through each record with a cursor... Can anyone help with that?

Thanks!

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Adrian's code does create and execute "one big update" statement as you've asked... have you tried it on a test table?

--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)

nicolas.pages SSC-Addicted Points: 430 More actions · Answer 4

Hi,

actually the code does not work since there is a small quote problem. Look here :

SELECT @sql = @sql + ' sc.name + ' = ' + @newValue + ', '

And I can't figure it out since I can't get an accurate idea of how the final query should exactly look like. I keep looking..

nicolas.pages SSC-Addicted Points: 430 More actions · Answer 5

Allright, got it!

Here's the correct code :

DECLARE @tableName VARCHAR(30)

DECLARE @newValue VARCHAR(30)

DECLARE @sql VARCHAR(2000)

/* Set Values */

SET @tableName = 'myTable'

SET @newValue = 'changeToThis'

/* Build Update statement */

SELECT @sql = 'UPDATE ' + @tableName + ' SET '

SELECT @sql = @sql + sc.name + ' = ' + @newValue + ', '

FROM sysobjects so

INNER JOIN syscolumns sc

ON so.id = sc.id

WHERE so.name = @tableName

AND sc.name LIKE 'aa[_]%'

/* Trim final comma */

SELECT @sql = LEFT(@sql, LEN(@sql) -1)

/* Review SQL Statement first */

PRINT @sql

So there was just one quote too many just before sc.name

Thanks again to you Adrian for the answer and to you Jeff for keeping me on the right track.

Cheers

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

Perfect. Thanks for the feedback.

Adrian... nice job!

--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)

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 7

Jeff,

Thanks! Although I will watch out for my typos before future postings! 😀

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Actually, Adrian's script is not quite perfect.

🙂

If any table or column name is happened to have spece or other "unnormal" character it will fail.

When you build any kind of generic scxripts you need to take care of it.

Also, if @newValue is not any kind of number Adrian's UPDATE will fail because it will interprete @newValue as a column identifier.

This must be better:

/* Build Update statement */

SELECT @sql = 'UPDATE ' + QUOTENAME(@tableName) + ' SET '

SELECT @sql = @sql + QUOTENAME(sc.name) + ' = ' + QUOTENAME(@newValue, '''') + ', '

FROM sysobjects so

INNER JOIN syscolumns sc

ON so.id = sc.id

WHERE so.name = @tableName

AND sc.name LIKE 'aa[_]%'

And you can avoid trimming last comma and do everything in one statement:

/* Build Update statement */

SELECT @sql = ISNULL(@sql + ',', 'UPDATE ' + QUOTENAME(@tableName) + ' SET ')

+ QUOTENAME(sc.name) + ' = ' + QUOTENAME(@newValue, '''')

FROM sysobjects so

INNER JOIN syscolumns sc

ON so.id = sc.id

WHERE so.name = @tableName

AND sc.name LIKE 'aa[_]%'

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

Heh... yeah... You're right and I agree... except I don't allow garbage names into the server. :hehe:

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 10

In "report specific" tables typically it's not up to you or me to define names.

😉

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 11

And sorry, Jeff, I edited my reply while you replied on it.

🙂

_____________
Code for TallyGenerator