我正在寻找一种方法来更新第3列,通过将其他2列的值相加 . 我遇到的问题是UPDATE语句似乎需要指定一个表,但我通过SELECT和JOIN语句使用“虚拟”表 . 这是我目前的代码:
SELECT *
FROM wp_posts AS p
LEFT JOIN (
SELECT tr.object_id AS id,
t.name AS physical
FROM wp_term_relationships AS tr
INNER JOIN wp_term_taxonomy AS x
ON (x.taxonomy='pa_physical-inventory'
AND x.term_taxonomy_id=tr.term_taxonomy_id)
INNER JOIN wp_terms AS t
ON t.term_id=x.term_id
) AS mo ON p.id = mo.id
LEFT JOIN (
SELECT tr.object_id AS id,
t.name AS murphy
FROM wp_term_relationships AS tr
INNER JOIN wp_term_taxonomy AS x
ON (x.taxonomy='pa_murphy-inventory'
AND x.term_taxonomy_id=tr.term_taxonomy_id)
INNER JOIN wp_terms AS t
ON t.term_id=x.term_id
) AS pa ON p.id = pa.id
LEFT JOIN (
SELECT post_id AS id, meta_value AS totalinventory
FROM wp_postmeta
WHERE meta_key = '_stock'
) AS totalinventory ON p.id = totalinventory.id
WHERE p.post_status = 'publish'
AND p.post_type = 'product'
我希望将“murphy”和“physical”添加到一起,并为返回的每一行插入“totalinventory” . 其中一些行返回“null”(如果“murphy”没有库存集它显示为null)所以我希望在将值一起添加时将其视为“0” .
任何帮助或指导都会非常感激,因为我长期以来一直在摸不着头脑 .
编辑:我在解决方案中对PHP开放,因为最终将使用cron作业运行 .
1 回答
在更简单的情况下,SELECT通常可以像这样转换为UPDATE ...
SELECT:
更新:
当然,如果你有LEFT JOIN,你首先需要SET, before 转换为UPDATE查询 .
免责声明:正如我在前言所述,根据情况和数据的细节,这并不总是有效 .