Gossamer Forum
Home : Products : Gossamer Links : Discussions :

find/ replace

Quote Reply
find/ replace
Hi,

I need help with come SQL commands please. I know this has been discussed many places, just would like some guidelines in the following specific examples as I don't want to mess up my database...

I would appericate a step-by-step guide..

1.
change Owner from admin > klaus in category x

2.
Replace in field 'Image' as follows:

now:
http://www.xxx.com/press/images/imagename.jpg

I want to replace it with:
http://www.yyy.com/news/images/imagename.jpg

Images name is the same

Thanks in advance

Klaus

http://www.ameinfo.com
Quote Reply
Re: [klauslovgreen] find/ replace In reply to
Hi,

1.

UPDATE lsql_Links SET LinkOwner = 'klaus'
WHERE LinkOwner = 'admin' AND
CategoryID = x

2.

UPDATE lsql_Links SET Image = REPLACE(Image, 'xxx.com/press', 'yyy.com/news');

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] find/ replace In reply to
Hi,

I tried the first one - but got the following error:

Quote:
Error: Query Error: Failed to execute query: 'UPDATE lsql_Links SET LinkOwner = 'peter' WHERE LinkOwner = 'admin' AND CategoryID = '1986'' Reason: Unknown column 'CategoryID' in 'where clause'

Should I not use CategoryID ?

Klaus

http://www.ameinfo.com
Quote Reply
Re: [klauslovgreen] find/ replace In reply to
Shouldn't it be CatID? Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [klauslovgreen] find/ replace In reply to
CategoryID is a column in the Category table and the update is being performed on the Links table. I'm not sure if you can specify two tables in updates so you may need to use GT::SQL
Quote Reply
Re: [Paul] find/ replace In reply to
any suggestion on how to do that?

Klaus

http://www.ameinfo.com
Quote Reply
Re: [klauslovgreen] find/ replace In reply to
Code:
my $sth = $DB->table('Links','CatLinks')->select( 'ID', { LinkOwner => 'admin', CategoryID => X });
while (my ($id) = $sth->fetchrow) {
$DB->table('Links')->update({ LinkOwner => 'Peter' }, { ID => $id });
}
Quote Reply
Re: [Paul] find/ replace In reply to
Cheers Paul.... ermm where do I execute this? SQL Monitor? or..

Klaus

http://www.ameinfo.com
Quote Reply
Re: [klauslovgreen] find/ replace In reply to
Its a perl script. I guess you could put it in a global and load a page with the global in and then remove it.
Quote Reply
Re: [klauslovgreen] find/ replace In reply to
Hi,

Sorry, I wasn't thinking properly (too early in the morning). Paul is right, you'll need a script for this (although we will be putting out MySQL 4 next week which supports multi table updates).

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] find/ replace In reply to
Quote:
we will be putting out MySQL 4 next week

How do you mean?
Quote Reply
Re: [Paul] find/ replace In reply to
Man, I must be really tired. =) I meant for our dedicated servers, we will be putting out a new package pretty soon with MySQL 4. We'll be emailing all our clients before it happens though.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] find/ replace In reply to
Hehe, for a moment I thought you'd taken over development from mysql.com Smile
Quote Reply
Re: [Paul] find/ replace In reply to
Cheers Alex and Paul - the perl script worked perfectly Paul - thanks!

Klaus

http://www.ameinfo.com