UPDATE query - @

Question

我需要对同一个名为organization_smd table的表执行递归调用。

我有4个数据字段。1.组织_id2.组织类型3.parent_organization_id(它是同一表中的另一个organization_id(4.primary_organization_id(当前值为空(

我需要使用organization_id查找并更新primary_organiation_id值，该值的organization_type='primary_MERCHANT'。

update organisation_smd os1 
inner join (
select organisation_id 
from organisation_smd 
where organisation_type = 'PRIMARY_MERCHANT'
and organisation_id = os1.organisation_parent_id
) as os2 
set os1.primary_organisation_id = os2.organisation_id 
where os1.organisation_id='6bc7c29d664c4f1eb3cb96b1e573b2ed';

我怎样才能做到这一点？我想在sub_query中使用organization_parent_id来获得我想要的输出。

Answer 1

联接需要一个ON子句，类似于：

on os2.organisation_id = os1.organisation_parent_id

您在WHERE子句中的子查询中有，但您必须将其删除，所以您可能想要这个：

update organisation_smd os1 
inner join (
select organisation_id 
from organisation_smd 
where organisation_type = 'PRIMARY_MERCHANT'
) as os2 on os2.organisation_id = os1.organisation_parent_id
set os1.primary_organisation_id = os2.organisation_id 
where os1.organisation_id='6bc7c29d664c4f1eb3cb96b1e573b2ed';