mysql - 如何更新和设置值到同一个表的子查询中的列

Question

我在运行此 SQL 时遇到了问题。基本上我想UPDATE 列sort_order 并将其设置为由同一表上的子查询选择的parents_sort_order。这是我的 SELECT 查询，显示了我需要的数据....

SELECT p.id,p.sort_order,p.parent_id, (
  SELECT `sort_order` AS parent_sort_order
  FROM `product`
  WHERE id = p.parent_id
)
FROM  `product` p
WHERE p.dealer_id !=0
AND p.vendor_id >100

谢谢!

Answer 1

加入您选择的版本:

SELECT p.id,p.sort_order,p.parent_id, b.sort_order as parent_sort_order
FROM  `product` p join `product` b
on p.id = b.parent_id
WHERE p.dealer_id !=0
AND p.vendor_id >100

更新选项 1:

update product a, product b set a.sort_order = b.parent_sort_order
where a.id = b.parent_id
and a.dealer_id !=0
and a.vendor_id > 100

更新选项 2:

update product a left join product b on a.id = b.parent_id 
set a.sort_order = b.parent_sort_order
where a.dealer_id !=0
and a.vendor_id > 100