Change the contents of 'some' of the rows in a table

Question

Change the contents of 'some' of the rows in a table

Jpotucek

SSCoach

Points: 18583
More actions
October 1, 2010 at 8:17 am

#227486

I have a table with 301 rows. 163 of these rows need to be changed.
select * from dbo.table where MyColumn LIKE '%images/pubs/columbia%' returns the 163 rows that need to be changed.
I need to change the LIKE '%images/pubs/columbia%' to LIKE '%en/graphics/columbia%' for just these 163 rows. Not really sure how to go about this. any suggestions?

Viewing 6 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I think the REPLACE function should be what you're looking for. Try using that, and post again if you're struggling with it.

John

Jpotucek SSCoach Points: 18583 More actions · Answer 2

Jpotucek

SSCoach

Points: 18583

October 1, 2010 at 8:32 am

#1230235

awesome! I'll give it a shot!!

Jpotucek SSCoach Points: 18583 More actions · Answer 3

Tried

Update dbo.table

Set MyColumn = replace(pageImage, 'images/pubs/columbia', 'en/graphics/columbia');

and

Update dbo.TableSet MyColumn = replace(pageImage, '%images/pubs/columbia%', '%en/graphics/columbia%');

Both Changed ALL rows in the column instead of just the rows returned by my original select???

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Yes, because you didn't include the WHERE clause. But even if it says 1000 rows affected, it will still only have actually made any changes to the ones where it finds the search string. Hope that makes sense.

John

Jpotucek SSCoach Points: 18583 More actions · Answer 5

you DA BOMB!!!

UPDATE dbo.table

SET MyColumn = replace(MyColumn, 'mages/pubs/columbia', 'en/graphics/columbia')

where MyColumn LIKE '%images/pubs/columbia%'

worked like a charm.