MySQL query find and replace values inside table

Question

We have a table "url_rewrite" in our Magento 2 store and we need to copy a lot of values inside this table. We have a column "store_id", that contains values from 1 until 4. Another column with "target_path" and the last column with "request_path".

Now we need to replace the "request_path" of all rows that contains the value "4" inside the "store_id" and where the "target_path" is the exact same of rows with "store_id" 2 and 4, with the value of rows with "store_id" 2.

So from the screenshot the row with "store_id" 4 should get the "request_path" value like "laptops/apple-macbook/apple-macbook-air-2023" from the row with "store_id" 2.

What's the exact sql we need to use for this?

Answer 1

Here is one way to do it with the update/join syntax:

update url_rewrite u
inner join url_rewrite u1 on u1.target_path = u.target_path
set u.request_path = u1.request_path
where u.store_id = 4 and u1.store_id = 2

Basically this selects rows with store_id 4 (alias u), and then attempts to joins with another row that has the same target_path and store_id 4. When the join matches, the query updates the original request_path to that of the matching row.

If you wanted a select rather than an update, we would probably use window functions rather than a self-join:

select entity_id, 
    case 
        when store_id = 4 then coalesce(new_request_path, request_path) 
        else request_path 
     end as request_path,
    target_path, redirect_type, store_id
from (
    select u.*,
        max(case when store_id = 2 then request_path end) 
            over(partition by request_path) as new_request_path
    from url_rewrite
) u