使用MySQL中同一个表的另一行中的值更新值

I need help with the following SQL:-

我需要以下SQL的帮助: -

UPDATE `mg_catalog_category_entity_varchar` AS t1 INNER JOIN `mg_catalog_category_entity`
AS t2 ON t1.`entity_id`= t2.`entity_id`
SET t1.`value` = CONCAT('US Sports Goods ', t1.`value`, ' | My Site Name') 
WHERE t2.`path` LIKE '1/2642%' AND t2.`level` > 1 AND t1.`attribute_id` = 40

My problem is that the t1.value field in the CONCAT() statement needs to pull the t1.value WHERE t1.attribute_id = 35 and I am struggling to figure out how to correctly specify this within the CONCAT() statement.

我的问题是CONCAT()语句中的t1.value字段需要提取t1.value WHERE t1.attribute_id = 35,我正在努力弄清楚如何在CONCAT()语句中正确指定它。

2 个解决方案

#1

Try this:

UPDATE `mg_catalog_category_entity_varchar` AS t1 INNER JOIN `mg_catalog_category_entity`
AS t2 ON t1.`entity_id`= t2.`entity_id`
SET t1.`value` = CONCAT('US Sports Goods ', (SELECT t3.`value` FROM `mg_catalog_category_entity_varchar` AS t3 WHERE t3.`attribute_id` = 35 LIMIT 1), ' | My Site Name') 
WHERE t2.`path` LIKE '1/2642%' AND t2.`level` > 1 AND t1.`attribute_id` = 40

#2

Try this:

UPDATE `mg_catalog_category_entity_varchar` AS t1 
INNER JOIN `mg_catalog_category_entity` AS t2 ON t1.`entity_id`= t2.`entity_id`
INNER JOIN
(
   SELECT *
   FROM mg_catalog_category_entity_varchar
   WHERE `attribute_id` = 35
) AS t12 ON t2.entity_id = t12.entity_id 
SET t1.`value` = CONCAT('US Sports Goods ', t12.`value`, ' | My Site Name') 
WHERE t2.`path` LIKE '1/2642%' 
  AND t2.`level` > 1 
  AND t1.`attribute_id` = 40