Geek Answers Handbook

How to run mySQL function to update all rows?

I am trying to remove HTML tags from all my records directly through MySQL. Thanks to StackOverflow this question , I found the following function: this type separates the html tags -

SET GLOBAL log_bin_trust_function_creators=1; DROP FUNCTION IF EXISTS fnStripTags; DELIMITER | CREATE FUNCTION fnStripTags( Dirty varchar(4000) ) RETURNS varchar(4000) DETERMINISTIC BEGIN DECLARE iStart, iEnd, iLength int; WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO BEGIN SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty )); SET iLength = ( iEnd - iStart) + 1; IF iLength > 0 THEN BEGIN SET Dirty = Insert( Dirty, iStart, iLength, ''); END; END IF; END; END WHILE; RETURN Dirty; END; | DELIMITER ; SELECT fnStripTags('this <html>is <b>a test</b>, nothing more</html>');

But I can’t find out how to use this function to update all records. E.g. I have entries in the Address myTable column from which I want to remove HTML tags using the above function. How can I directly update all records of the Address column with the specified function, or if direct updating is not possible, is there any way to insert all updated records in the second column of the Address_Stripped table?

PS, I know, in my question, no research was conducted to get the answer myself, but this is simply because I do not know how to look for it.

+5
source share
2 answers

You just need to call this function in the update statement:

 UPDATE mytable SET address = fnStripTags(address)
+6
source

It depends on how many rows you have to update, and you may or may not lock the entire table for the update time.

If the table is quite small or might be locked for update time:

 UPDATE table_name SET address = fnStripTags(address)

And if the table is large and / or you cannot lock the table for the entire update time - you must perform these updates in a loop, in pieces with ORDER BY primary_key

 UPDATE table_name SET address = fnStripTags(address) WHERE primary_key > <previous_value> ORDER BY primary_key LIMIT 1000

(you can use any suitable limit)

+3
source

More articles:


All Articles