mysql query to update field with the value of other field in the same table

Question

I have a table with these fields:

id_post

meta_key

meta_value

The query should grab the value of "name_value" where "meta_key" match a given text and remove it from the value of "meta_value" where "meta_key" is "sku".

Let say I have these rows:

id_post -- meta_key -- meta_value

101 -- myCustomText -- train

101 -- sku -- cartrain

102 -- myCustomText -- boat

102 -- sku -- carboat

Then after the query is run the results should be:

id_post -- meta_key -- meta_value

101 -- myCustomText -- train

101 -- sku -- car

102 -- myCustomText -- boat

102 -- sku -- car

In words the query should do this:

FOR EACH ROW WITH THE SAME "id_post" SELECT VALUE OF "meta_value" WHERE " meta_key" == "myCustomText" AND SUSTRACT ITS VALUE FROM "meta_value" WHERE "meta_key" == "sku"

Answer 1

Use replace()

update demo
set sku = replace(sku, name_variation, '')

DEMO

Or to show the table as desired view

select id_post,
name_variation,
replace(sku, name_variation, '') sku
from demo

DEMO

Edit for updated question

use a join with same table but with additional filter on on clause

update demo a  
join demo b on (a.id_post = b.id_post and b.meta_key = 'myCustomText' )
set a.meta_value = replace(a.meta_value, b.meta_value, '')
where a.meta_key = 'sku'

Update

select *,
replace(a.meta_value, b.meta_value, '') sku
from demo a 
join demo b on (a.id_post = b.id_post and b.meta_key = 'myCustomText' )
where a.meta_key = 'sku'

Select