Scenario: A database contains many links like

http://www.foo.com/page1.htm
http://www.foo.com/page2.htm
...

Question: How could one change all these links to

http://www.foo.net/page1.htm
http://www.foo.net/page2.htm
...

(what means changing ".com" to ".net")

Can I send a single command using the SQL-Monitor?

Many thanks for any hint!

Peter


[This message has been edited by Peter D (edited April 19, 2000).]

The answer is 'yes'

What the command would be is another story <G>

I'm not awake enough to attempt to put something so potentially dangerous into writing.

The idea is you want to

SELECT * FROM Links where URL LIKE 'http://www.foo.com/%'

Then you want to change the '.com/' portion to '.net/'

REPLACE(URL, '.com/', '.net/')

so the whole command would be:

SELECT * FROM Links where URL LIKE 'http://www.foo.com/%'
REPLACE(URL, '.com/', '.net/')


Try it on a dummy table first! Anything that changes your main database should be tried out on a test set of data first!!

pugdog,

Goood Morning and many thanks! :-)

This sounds pretty simple and looks like it should work, but SQL Monitor says "Enter in a _single_ SQL command"

I played around with your suggestion (on a dummy database of course ...) - alas the additional REPLACE command just produces a parse error ...

Did you have a coffee already? I mean: can you think of a solution? ;-)

Peter

I think:

UPDATE Links SET URL = REPLACE(URL, 'www.foo.com', 'www.foo.net')

would do the trick. Make sure to test on a backup first.

Cheers,

Alex

I've been leading a very strange life if you can tell by the times of my posts.

Did Alex's revision work? I told you I wasn't awake yet

Yes, this worked! Many thanks to Alex (and best wishes for you, pugdog ...) :-)

Alex, btw: We recently had a press campaign which lead to about 60,000 visitors per day with estimated 5 searches per second on a 16,000+ database. Links SQL performed like a charm! Just thought you would like to know ... ;-)

Peter

Peter - what hardware are you on?

Well - let me see ... 8-)

It's a 750 MHz Dual-PIII, 1 GB RAM, RAID 0/1 with 5 SCSI-Drives. Apache 1.3.9 running on FreeBSD.

Fortunately I was upgrading this machine to its current state just 2 days before the big rush, otherwise ... :-<

Peter

I have a Sun Sparc Ultra 10 with 128 meg and 2 4 gig hard drives. At it's peak it was handling over 800,000 page hits (.html/.cgi) a day, with about 20,000 of it being 'search'.

Machine handled it ok.

Links SQL and indexed searches don't seem to be a problem at all.

I was doing some non-indexed searches, and the performance differences are staggering!

The diskspace and RAM is worth it for the performance boost!

Thanks peter, good to hear!

For those that are curious, non indexed means doing a search like '%some term%'. It is very simple to do, however mysql needs to search every single row in the database. Even mysql takes awhile when doing this.

Links SQL indexes the words in a series of tables to optimize speeds, and never has to do a full table scan.

Cheers,

Alex