Table not updating. You can't specify target table 'nodeassociation' for update in FROM clause

Question

I have a table abc and I am trying to update it but I keep getting the following error: You can't specify target table 'abc' for update in FROM clause.

Below is my SQL query:

UPDATE abc
SET value_1 = xxx
WHERE value_2 = 'ABC' AND id 
IN (Select id FROM abc WHERE value_3 = 'XYZ' AND value_1 = xxx);

Could anyone please tell me how I can make this query work? Many thanks.

Your error message says nodeassociation, but your code has no such table. Very odd. — jarlh, Jul 10 '17 at 10:12
Have you checked this [answer](https://stackoverflow.com/a/14302701/3367442)? — Sahil Patel, Jul 10 '17 at 10:21
@SahilPatel Yes, this should be flagged as a duplicate of that. — underscore_d, Jul 10 '17 at 10:23
Possible duplicate of [You can't specify target table for update in FROM clause](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) — Sahil Patel, Jul 10 '17 at 10:24
@SahilPatel I _think_, anyway :) I've not used MySQL much. But it's hard to tell with this question because the title and body don't make any sense together as they seem to refer to different tables. — underscore_d, Jul 10 '17 at 10:26
So you should post the actual code you run, not a paraphrased version. — underscore_d, Jul 10 '17 at 10:56

score 0 · Answer 1 · answered Jul 10 '17 at 10:15

Whenever you use more than one table in an update statement - like you use subqueries or joins - you have to give the table, which has to be updated an alias (like i called the table myupd_table).
so you were just missing the FROM-part and the alias

UPDATE myupd_table
   SET value_1 = xxx
   FROM abc myupd_table
  WHERE myupd_table.value_2 = 'ABC' 
    AND myupd_table.id IN (Select id FROM abc WHERE value_3 = 'XYZ' AND value_1 = xxx);

score 0 · Answer 2 · answered Jul 10 '17 at 12:56

0

Following worked for me:

UPDATE abc
SET value_1 = xxx
WHERE value_2 = 'ABC' AND id 
IN (Select t1.id FROM (select * from abc) as t1 
WHERE t1.value_3 = 'XYZ' AND t1.value_1 = xxx);

answered Jul 10 '17 at 12:56

Tbas

1
1

score 0 · Answer 3 · answered Jul 10 '17 at 15:23

0

If id is a key (probably primary key) of your table, why not using this query instead:

UPDATE abc
   SET value_1 = xxx
 WHERE value_2 = 'ABC' 
   AND value_3 = 'XYZ'
   AND value_1 = xxx;

otherwise consider using better names for your fields.

answered Jul 10 '17 at 15:23

Amir Pashazadeh

7,170
3
39
69

Table not updating. You can't specify target table 'nodeassociation' for update in FROM clause

3 Answers3