search and replace query for a value in every column

[mcnews]

how can i search and replace a value in every or selected columns?

tia,
mcnewsxp

 

[Gene Wirchenko]

how can i search and replace a value in every or selected columns?

update thetable
set selectedcolumn=newvalue
where selectedcolumn=searchvalue
and repeat as needed.

Sincerely,

Gene Wirchenko

 

[John W. Vinson]

how can i search and replace a value in every or selected columns?

tia,
mcnewsxp

How depends on what. In a properly normalized table, replacing a value in
every column should be a vanishingly rare operation! Each column would be a
different "Attribute" of the entity represented by the table; it would make no
sense at all to update (say) the LastName, Birthdate, Height and EyeColor
fields replacing the same value in all.

What's the structure of your table, and what change are you proposing?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mcnews]

How depends on what. In a properly normalized table, replacing a value in

every column should be a vanishingly rare operation! Each column would bea

different "Attribute" of the entity represented by the table; it would make no

sense at all to update (say) the LastName, Birthdate, Height and EyeColor

fields replacing the same value in all.



What's the structure of your table, and what change are you proposing?

--



John W. Vinson [MVP]

Microsoft's replacements for these newsgroups:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/

http://social.answers.microsoft.com/Forums/en-US/addbuz/

and see also http://www.utteraccess.com

i receive data from different places in different formats that in theory issupposed to be the same data. i have no control how over column names orhow each place stores the same type of info. so much scrubbing is to be done. so after doing the analysis on each data source i can tell what data is junk and needs to vanish.

 

[John W. Vinson]

i receive data from different places in different formats that in theory is supposed to be the same data.

i have no control how over column names or how each place stores the same
type of info. so much scrubbing is to be done. so after doing the analysis
on each data source i can tell what data is junk and needs to vanish.


I guess I still don't understand the question.

It sounds like you have arbitrary tables, with arbitrary and unpredictable
fieldnames, with arbitrary and unpredictable contents - and you're asking for
some automated procedure to whip it into shape. I don't think the science of
artificial intelligence has progressed to that extent!

Could you please explain what it is you want us volunteers to suggest or
provide?

I can imagine a table with fields like Tablename, Columnname, NewColumnName,
and a form to manage it, but beyond that I'm just not sure what you're asking!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mcnews]

i have no control how over column names or how each place stores the same

type of info. so much scrubbing is to be done. so after doing the analysis

on each data source i can tell what data is junk and needs to vanish.





I guess I still don't understand the question.



It sounds like you have arbitrary tables, with arbitrary and unpredictable

fieldnames, with arbitrary and unpredictable contents - and you're asking for

some automated procedure to whip it into shape. I don't think the science of

artificial intelligence has progressed to that extent!



Could you please explain what it is you want us volunteers to suggest or

provide?



I can imagine a table with fields like Tablename, Columnname, NewColumnName,

and a form to manage it, but beyond that I'm just not sure what you're asking!

--



John W. Vinson [MVP]

Microsoft's replacements for these newsgroups:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/

http://social.answers.microsoft.com/Forums/en-US/addbuz/

and see also http://www.utteraccess.com

i worked it out. i just loop through each row of the table and loop through the field names. i then do a string compare with a loaded string array and if i find what is in the array i execute an update statement on the entire column. it works fine.
thanks.