MySQL package update

Question

I have 2 tables (MySQL)

• data_details
• accounts_invoices

Ideally, each data_details should have an account_invoices identifier. (data_details has a foreign key with primary key account_invoices)

For some reason, there are data_details records where account_invoice_id does not exist in the accounts_invoices table

So I tried updating these data_details entries with the famous account_invoice identifier. this is what i did

update data_details 
set account_invoice_id = 1
where account_invoice_id in (
  select d.id
  from data_details d left join accounts_invoices a
  on d.account_invoice_id = a.id
  where a.id is null    
)

But an error occurs, saying

You can specify the target table 'data_details' for updating in the FROM clause (error 1093)

Can someone help me, thanks in advance

amuses

Sameera

Answer 1

, , , , . , - , :

update data_details 
set account_invoice_id = 1
where account_invoice_id in (
select * from (
  select d.id
  from data_details d left join accounts_invoices a
  on d.account_invoice_id = a.id
  where a.id is null    
) as t
)

, , , .

---

SQL, , .

update data_details 
set account_invoice_id = 1
where id in (
select * from (
  select d.id
  from data_details d left join accounts_invoices a
  on d.account_invoice_id = a.id
  where a.id is null    
) as t
)