SQL Update and Replace across ENTIRE table

Answering4uAnt
Answering4uAnt used Ask the Experts™
on
Right, this may not be possible but i'll ask anyway.

Is it possible to write a SQL statement to just say update entire table and replace ALL occurances of x with y across all columns.
If it can't be done, then i'll have to do it the long way round with UPDATE and REPLACE across each specific column

Thanks in Advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
run this, copy the results and run the results on a new query window


SELECT 'UPDATE '+Table_Name +'  SET '+COLUMN_NAME +'  =  REPLACE('+COLUMN_NAME+' ,''x'',''y'' )  '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'urTableName'

Commented:
There's no easy statement working on all columns.
You may list columns and use dynamic queries or just query column by column.
awking00Information Technology Specialist

Commented:
See attached.
query.txt

Author

Commented:
Aneeshattingal, I was expecting it not to be possible and though it wasn't directly possible, your post did as good as I could have ever expected and did all the hard work in writing a long winded SQL update statement for me.

Cheers, you have saved me a lot of time

Author

Commented:
Points increased to 500, thanks for all your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial