mysql - 使用内连接和动态更新进行选择

Question

我在我的 magento 数据库中创建了一个查询，以查找具有“New From”数据但没有“New To”的产品。

SELECT 
catalog_product_entity.entity_id,
catalog_product_entity.sku,
a.value as New_From,
b.value as New_To
FROM `catalog_product_entity`
INNER JOIN catalog_product_entity_datetime as a on a.entity_id = catalog_product_entity.entity_id and a.attribute_id = '93'
INNER JOIN catalog_product_entity_datetime as b on b.entity_id = catalog_product_entity.entity_id and b.attribute_id = '94'
WHERE a.value IS NOT NULL AND b.value IS NULL

现在我想完成什么，因为它涉及 500 多个项目，在此查询上运行 UPDATE 语句，在打开后 1 个月关闭“New To”。

有什么想法如何运行它吗？

澄清一下:

表 1:(catalog_product_entity)

|  entity_id  |  sku  |
|  ---------  |  ---  |
|      1      |  ABC  |
|      2      |  DEF  |

表 2:(catalog_product_entity_datetime)

|  id  |  entity_id  |  attribute_id |         value         |
|  --- |  ---------  |  ------------ |         -----         |
|   1  |      1      |       93      |  2013-06-12 00:00:00  |
|   2  |      1      |       94      |  2013-07-12 00:00:00  |
|   3  |      1      |       98      |  Some other attribute |
|   4  |      2      |       93      |  2014-08-20 00:00:00  |
|   5  |      2      |       94      |        NULL           | <- This I want updating
|   6  |      2      |       98      |  Some other attribute |

我想用同一个表中的日期 + 1 个月更新表 2 中的 NULL 值。我的搜索返回:

|  entity -id  |  sku  |       New_From       |        New_To         |
|  ----------- |  ---  |  ------------------- |  -------------------  |
|       2      |  DEF  |  2014-08-20 00:00:00 |         NULL          |

所以我想编写将该字段更新为 New_From + 1 个月的查询。

|  entity -id  |  sku  |       New_From       |        New_To         |
|  ----------- |  ---  |  ------------------- |  -------------------  |
|       2      |  DEF  |  2014-08-20 00:00:00 |   2014-09-20 00:00:00 |

在我看来，我最终会得到这样的结果:(但这不起作用)

UPDATE catalog_product_entity_datetime
SET value = TIMESTAMP(a.value+300000000)
INNER JOIN catalog_product_entity_datetime as a on a.entity_id = catalog_product_entity_datetime.entity_id and a.attribute_id = '93'
WHERE attribute_id=94

Answer 1

试试这个:

UPDATE catalog_product_entity_datetime t1
JOIN catalog_product_entity t2
ON t1.entity_id = t2.entity_id
AND t1.attribute_id = 94
JOIN catalog_product_entity_datetime t3
ON t2.entity_id = t3.entity_id
AND t3.attribute_id = 93
SET t1.value = CASE WHEN t1.value IS NULL THEN date_add(t3.value, interval 1 month)
                    ELSE t1.value END
;

Demo Here