SQL - #1093 - Table '' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Question

I have this code:

UPDATE WORLD_TEST 
SET PROGRAMMER = (SELECT (RECURSE_HOUR/360) 
                  FROM WORLD_TEST_PROGRAMMER, WORLD_TEST 
                  WHERE LVL = LVL_PROGRAMMER) 
WHERE (SELECT MAX_RECURSE 
       FROM WORLD_TEST_PROGRAMMER, WORLD_TEST 
       WHERE LVL = LVL_PROGRAMMER) 
>= 
(PROGRAMMER+(SELECT (RECURSE_HOUR/360) 
             FROM WORLD_TEST_PROGRAMMER, WORLD_TEST 
             WHERE LVL = LVL_PROGRAMMER));

With this error:

1093 - Table 'WORLD_TEST' is specified twice, both as a target for 'UPDATE' and as a separate source for data

EDIT (clarification from comments): PROGRAMMER and LVL_PROGRAMMER is from WORLD_TEST table, and RECURSE_HOUR, LVL, MAX_RECURSE is from WORLD_TEST_PROGRAMMER table.

Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 09 '18 at 09:35
Error message is quite clear, that you cannot use the sample table twice in `update` clause. There are other ways to achieve that, like using Derived Tables. But your current query in itself is extremely convoluted — Madhur Bhaiya, Nov 09 '18 at 09:36
Please explain what you are trying to do with relevant sample data, and expected output. — Madhur Bhaiya, Nov 09 '18 at 09:36
I want to update the resources based in other table and if the resources doesn't exceed the allowed limit — Victor, Nov 09 '18 at 09:39
RECURSE_HOUR, LVL, LVL_PROGRAMMER, MAX_RECURSE are columns in which table, respectively ? — Madhur Bhaiya, Nov 09 '18 at 09:40
PROGRAMMER and LVL_PROGRAMMER is from WORLD_TEST and RECURSE_HOUR, LVL, MAX_RECURSE is from WORLD_TEST_PROGRAMMER — Victor, Nov 09 '18 at 09:42

Madhur Bhaiya · Accepted Answer · 2018-11-09T10:34:55.177

Error message is quite clear, that you cannot use the same table in the UPDATE clause as well as the source of a subquery. There are other ways to achieve that, like using Derived Tables, etc. In this particular case, you simply need to JOIN between the two tables.

Also, please don't use Old comma based Implicit joins and switch to Modern Explicit Join based syntax

In case of multi-table queries, it is recommended to use Aliasing for code readability and avoiding ambiguous behavior.

Based on your further clarification in the comments, try the following:

UPDATE WORLD_TEST AS wt 
JOIN WORLD_TEST_PROGRAMMER AS wtp 
  ON wt.LVL_PROGRAMMER = wtp.LVL 
SET wt.PROGRAMMER = wtp.RECURSE_HOUR
WHERE wtp.MAX_RECURSE >= (wt.PROGRAMMER + (wtp.RECURSE_HOUR/360))

@Victor These are aliases. It is recommended to use them in multi-table queries for code readability and avoiding ambiguous behaviour. Refer this tutorial: http://www.mysqltutorial.org/mysql-alias/ — Madhur Bhaiya, Nov 09 '18 at 10:33

SQL - #1093 - Table '' is specified twice, both as a target for 'UPDATE' and as a separate source for data

1 Answers1