CODEWITHSUNDEEP
mysqlsqlsql-updateupdates
Victor
Victor

Reputation: 29

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

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.

Upvotes: 1

Views: 2211

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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))

Upvotes: 2

Related Questions