Tag Archives: Table is specified twice

SQL Error: Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data

For example, SQL reports an error: table is specified twice, both as a target for 'Update' and as a separate source for data . The table is specified twice as both an update object and an independent data source.

Error reporting scenario: query the difference set of two tables and update the records. For example: perform an associated query on tables a and B to find out the records in table a that exist but not in table B, and then update a field in table a to mark.

Error reporting SQL:

UPDATE company AS f SET related = 0 WHERE uid IN 
(
select c.uid FROM company AS c 
LEFT JOIN member AS m ON m.uid=c.uid 
WHERE m.uid IS NULL
)

Solution:

Since a table cannot be both an update object and an independent data source, change one of them. Update is the main sentence, which cannot be removed, so you can only modify the table as the data source part. Take the results of the two table joint query as a temporary table and add a layer to the outer layer for query. In this way, the data source becomes a temporary table instead of the two tables for the previous associated query.

UPDATE company AS f SET related = 0 WHERE uid IN (
SELECT * FROM 
(
select c.uid FROM company AS c 
LEFT JOIN member AS m ON m.uid=c.uid 
WHERE m.uid IS NULL
) AS d
)