MySQL: using REPLACE with a JOIN?

You need to specify a Join condition, for example, like this:

UPDATE texts_table AS tt
INNER JOIN values_table AS vt 
   on tt.valueId = vt.valudId /*or whatever the join condition*/ 
SET tt.Text= REPLACE(tt.Text, vt.SearchValue, vt.ReplaceValue)

As specified in the syntax of the UPDATE clause:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

The table_references clause lists the tables involved in the join. Its syntax is described JOIN Syntax .

A JOIN is the wrong approach. The resulting rows of a JOIN are created by combining single rows from each joined table. This isn't a good match for what you want to do. For a SELECT, a JOIN combined with a GROUP BY would make a little more sense, though there's no GROUP BY clause in an UPDATE, so this isn't an option.

SQL isn't designed for this sort of text manipulation. It should be performed by a programming language, not a data language.

For a SELECT, you could force SQL to do the work by writing a user defined aggregate function, something like:

/**
 * make_string: Strings hold both character sequence and a length. Strings can 
 * hold null characters and are null terminated for safety, in case they hold 
 * C-strings, but the null character shouldn't be used as a terminator by any 
 * string function.
 *
 * @param data: characters to copy to string
 * @param len: number of characters to copy from data
 * @param size: number of characters to allocate; lets you allocate extra space. 
 *     If size < len, size is set to len, so you can pass 0 to not allocate 
 *     extra space.
 */
string_t* make_string(const char *data, int len, int size);
string_t* delete_string(string_t* str);
char* string_data(string_t* str);
int string_length(string_t* str);
/**
 * Copy data to str's buffer, replacing whatever was stored there previously.
 *
 * @returns 0 on success, non-0 on failure.
 */
int string_set(string_t* str, const char* data, int len);
/**
 * Replace first occurrence of 'search' in 'str' with 'replace', starting the 
 * search at character 'start'.
 *
 * If there isn't enough space in str's buffer, resize the buffer. If there is 
 * enough space, must always succeed.
 *
 * @returns position of character after replaced section, or -1 if search isn't found.
 */
int string_replace(string_t* str, string_t* search, string_t* replace, int start);
...

my_bool group_replace_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
    if (args->arg_count != 3) {
        strcpy(message,"wrong argument count: group_replace(str, search, replacement) requires three string arguments");
        return 1;
    }
    initid->maybe_null = 1;
    initid->max_length = args->lengths[0];
    if (! (initid->ptr = make_string("", 0, args->lengths[0])) ) {
        snprintf(message, MYSQL_ERRMSG_SIZE, "error allocating string for group_replace: %s", strerror(errno));
        return 1;
    }
    return 0;
}

void group_replace_deinit(UDF_INIT *initid) {
    delete_string(initid->ptr);
}

void group_replace_clear(UDF_INIT *initid, char *is_null, char *error) {
    string_set(initid->ptr, "", 0);
    // result will be null, until 
    *is_null = 1;
    *error = 0;
}

void group_replace_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) {
    if (*error) {
        return;
    }
    if (*is_null && args->args[0]) {
        if (string_set(initid->ptr, args->args[0], args->lengths[0])) {
            *error = 1;
            return;
        }
        *is_null = 0;
    }
    string_t *search, *replacement;
    if (! (search = make_string(args->args[1], args->lengths[1])) ) {
        *error = 1;
        return;
    }
    if (! (replacement = make_string(args->args[2], args->lengths[2])) ) {
        delete_string(search);
        *error = 1;
        return;
    }
    int pos=0;
    do {
        pos = string_replace(initid->ptr, search, replacement, pos);
    } while (0 <= pos);
}
char* group_replace(UDF_INIT *initid, UDF_ARGS *args,
                    char *result, unsigned long *length,
                    char *is_null, char *error)
{
    if (*is_null) {
        *length = 0;
        return null;
    }
    *length = string_length(initd->ptr);
    return string_data(initid->ptr);
}

The above:

• is untested.
• will (presumably) be case-sensitive (depending on how you implement string_replace), which differs from the behavior of MySQL's other string functions. For case insensitivity, implement and use a string_ireplace function.

Implementation of the string functions left as an exercise.

The corresponding SELECT statement would be:

SELECT tt.id, GROUP_REPLACE(tt.Text, vt.SearchValue, vt.ReplaceValue)
  FROM texts_table AS tt
    JOIN values_table AS vt ON INSTR(tt.Text, vt.SearchValue) > 0
  GROUP BY tt.id
;