MySQL batch update

Question

I have 2 tables (MySQL)

data_details
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

To avoid this problem in the future, I recommend using InnoDB and foreign keys constraints. — Znarkus, Jun 01 '11 at 08:10
Hi @Znarkus, thanks for the comment, basically I'm also trying to add a foreign key relationship to this existing table :D (data_details) — sameera207, Jun 01 '11 at 08:11
not tested but mysql permit thos query : UPDATE data_details SET d1 account_invoice_id=1 FROM data_details d1 INNER JOIN data_details d2 ON d1.account_invoice_id = d1.id LEFT JOIN accounts_invoices a on d2.account_invoice_id = a.id WHERE a.id is null — Brice Favre, Jun 01 '11 at 08:17
Hi @Brice, Thanks for the answer... but I'm getting this error.. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM data_details d1 INNER JOIN data_details d2 ON d1.account_invoice_id = d1.id' at line 2 any idea?? — sameera207, Jun 01 '11 at 08:32
Hi @Brice, I changed it as d1.account_invoice_id = d2.id, but still the same error.... thanks again.. — sameera207, Jun 01 '11 at 08:43

Znarkus · Accepted Answer · 2011-09-07T20:07:06.873

4

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
)

edited Sep 07 '11 at 20:07

answered Jun 01 '11 at 08:13

Znarkus

21,120
20
71
104

Hi @Znarkus, this works with out an error, but nothing is updating.. any idea ? thanks for the help cheers sameera – sameera207 Jun 01 '11 at 08:27
Try to extract the `select` statements and see what they return – Znarkus Jun 01 '11 at 08:33
Hi Select statement returns the correct id's as id.. So the select is correct.... so its not simply updating the records.. any help would be appreciated, thanks – sameera207 Jun 01 '11 at 08:39
Yepee....it Works..... I just changed the where condition to account_invoice_id to id... thanks a lot.... – sameera207 Jun 01 '11 at 09:00

MySQL batch update

1 Answers1