英文:
MySQL query find and replace values inside table
问题
我们的Magento 2商店中有一个名为"url_rewrite"的表，我们需要复制这个表中的许多值。
我们有一个名为"store_id"的列，其中包含从1到4的值。另一个列名为"target_path"，最后一列名为"request_path"。
现在，我们需要替换所有包含"store_id"值为"4"且"target_path"与"store_id"为2和4的行完全相同的行的"request_path"值，将其替换为"store_id"为2的行的值。
所以根据屏幕截图，"store_id"为4的行应该获取"store_id"为2的行的"request_path"值，即"laptops/apple-macbook/apple-macbook-air-2023"。
我们需要使用的确切SQL是什么？
英文:
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?
答案1
得分: 1
以下是使用update/join语法执行此操作的一种方式：
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
基本上，这个查询选择具有store_id为4（别名u）的行，然后尝试与具有相同的target_path和store_id为2的另一行进行连接。当连接匹配时，查询将原始的request_path更新为匹配行的值。
如果您想要一个select而不是一个update，我们可能会使用窗口函数而不是自连接：
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论