MySQL batch update

Question

I have 2 tables (MySQL)

1. data_details
2. accounts_invoices

Ideally every data_details should have an accounts_invoices id. (data_details has a foreign key with accounts_invoices's primary key)

For some reason there are data_details records where there accounts_invoice_id doesn't exist in accounts_invoices table

So I tried to update those data_details records with a known accounts_invoice id. 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 target table 'data_details' for update in FROM clause (error 1093)

can someone help me, thanks in advance

cheers

sameera

Answer 1

Now this might be a wild guess, but I think the problem is that you update the same table you're querying. I think the work-around is to use a temporary table, like this:

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
)

Haven't tried it though, so might be all wrong.

---

Updated the SQL to fix my error that was spotted in the comments.

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
)