mysql - 更新分配在不同表中的发票 ID 的代码

Question

我正在使用不同的表，使用联合来删除重复项并为我提供结果

现在我想用更新查询更新结果，我该如何做

这是我的查询

select zohoinvoice_id as invoice_id,status,'' as link from zoho_details where active = 0
            union
            select invoice_id,status,'' as link from latest_invoices where active = 0
            union
            select invoice_id,status,'' as link from search_invoices where active = 0
            union
            select zohoinvoice_id as invoice_id,status,'gen' as link from zoho_details where active = 1 AND status = 'Void'

我想更新 if update table set gen = '' where Invoice_id in (上面的查询与不同的表连接

Answer 1

我会将条件分成多个 where 表达式:

update table t
    set gen = ''
    where t.invoice_id in (select zohoinvoice_id from zoho_details where active = 0) or
          t.invoice_id in (select invoice_id from latest_invoices where active = 0) or
          t.invoice_id in (select invoice_id from search_invoices where active = 0) or
          t.invoice_id in (select zohoinvoice_id from zoho_details where active = 1 AND status = 'Void');

多个表达式使优化器更容易优化查询。我还应该注意到，第一个和第四个条件可以组合成一个子查询。