There's a SQL technical limitation where one may not update a table based on its nested View. Thus, the work around for that would be creating an alias table with the returns of a Select statement. Then, it would be possible to run an update on the original table based on the new alias. A demonstration is as follows:
CREATE TABLE a_random
SELECT g2_Entity.g_id, g2_Entity.g_onLoadHandlers
FROM g2_Derivative
INNER JOIN g2_ChildEntity ON g2_Derivative.g_id = g2_ChildEntity.g_id
INNER JOIN g2_Entity ON g2_Derivative.g_id = g2_Entity.g_id
WHERE
g2_ChildEntity.g_parentId IN
(SELECT g_id FROM g2_Entity WHERE g_entityType = 'GalleryAlbumItem')
AND g2_Derivative.g_derivativeType = 1;
---------------------------------------------------------------------------------------------------------------
UPDATE g2_Entity a, a_random c
SET a.g_onLoadHandlers='|RandomHighlight|'
WHERE a.g_id = c.g_id
The example above is an actual SQL statement ran against a mySQL database. The exact application is Gallery2 RandomHighlight module's recursive update on nested folders.

================================================================================================

How to do a Search and Replace in mySQL:
update `_vldmembers` set username = replace(username, '%', '_')
Categories: