SQL Inner Query In Replace Statement

Question

I would like to replace a certain part of a columns field with something else. The only issue is that what I've tried doesn't work. I'm using HeidiSQL

This is a overview of the database: Database Overview

What I would like to do is for each user_id with field_id 523 I would like to replace 'centraal-zorgportaal.nl/afbeeldingen/' with '/profiles/(for each user id here)/'.

So the end result example after running the query will look like this: '/profiles/711/logo.gif', but then for each user with a diffrent logo.gif.

Note: Can't post more than two links so removed the http etc. in front.

This is the query I've tried and dind't work:

update wp_bp_xprofile_data 
   set value = replace( value,
                        'http://www.centraal-zorgportaal.nl/afbeeldingen/',
                        '/profiles/' + 
                        (select user_id 
                           from wp_bp_xprofile_data 
                          where user_id = @n := @n + 1 n) + 
                        '/') 
  where field_id = 523

(Table name is: wp_bp_xprofile_data)

This is the error message I received: Error message:

Could anyone explain why this doesn't work, how to fix it and the best way to approach this problem?

Answer 1

In MySQL/MariaDB, you cannot refer to the table being modified. From your description, you seem to want something like this:

update wp_bp_xprofile_data
    set value = replace(value,
                        'http://www.centraal-zorgportaal.nl/afbeeldingen/', 
                        concat('/profiles/', user_id, '/')
                       )
    where field_id = 523;