Updating a specific column ( Special case )

[sukatoa]

I have a table, where it composed of several columns, but i am concerning only on a specific column with datatype varchar under that db table...

Now, for example:

column: links
values:

http://localhost/asdf/asdf.php
http://localhost/
http://localhost/12312/adfdf.php
http://localhost/ghierew/adfd.html


Now, i would like to update the said column such that it only replace the
'http://localhost/' to some value or let us say an existing website rootlink 'http://www.google.com. ph/'

that will result into

http://www.google.com.ph/asdf/asdf.php
http://www.google.com.ph/
http://www.google.com.ph/12312/adfdf.php
http://www.google.com.ph/ghierew/adfd.html

I expect from this db table to have millions of row, therefore i doubt to implement my idea of replacing them one by one at a time because this db table is just acting like a temporary storage that will be truncated by the time the control system will demand a new temporary storage... and it takes little time to process... Thread-timing bug will arise

I believe that the MySQL Server will process this just in time.

Is it possible to do it on just a query? or queries? Java is the front-end

[Atli]

Hey.

The query to update a table in such a way is simple enough:

UPDATE `tbl`
SET `links` = REPLACE(`links`, 'http://localhost/', 'http://www.google.com.ph/');

I'm not sure how that will work with you temporary truncation scenario, but if your table gets truncated/re-created often, and you need this to happen for every row that get's inserted, you could try creating a Trigger that does this automatically each time a INSERT statement is issued.

CREATE TRIGGER `tbl_links_insert` BEFORE INSERT ON `tbl`
FOR EACH ROW
BEGIN
    NEW.links = REPLACE(NEW.links, 'http://localhost/', 'http://www.google.com.ph/');
END;

This could create a performance bottle-neck though, so you should look out for that.

[sukatoa]

Thank you for your reply Atli :)