Question 191624 in Magento Question posted in
The official documentation can be found here.

Magento – MySQL query find and replace values inside table

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.

enter image description here

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    From what you’ve described, it sounds like you need to update the request_path of all rows with store_id = 4 based on the request_path from the rows with the same target_path and store_id = 2.

    Here is a SQL command that can achieve this:

    UPDATE url_rewrite AS t1 
INNER JOIN url_rewrite AS t2 ON t1.target_path = t2.target_path 
SET t1.request_path = t2.request_path 
WHERE t1.store_id = 4 AND t2.store_id = 2;

    The UPDATE clause updates the url_rewrite table, which we alias as t1.

    The INNER JOIN clause combines rows from url_rewrite (aliased as t2) and t1 if they have the same target_path.

    The SET clause changes the request_path of t1 to the request_path of t2.

    The WHERE clause specifies the condition under which the UPDATE statement will be executed – when store_id is 4 in t1 and 2 in t2.

    Please create a database backup before running the above query or run it first on a development environment, as it may result in irreversible changes. Also, the question may take a long time, depending on the size of your table. You might want to consider running it in a low-traffic period.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search