Tag Archives: SQL

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
)

Oracle executes SQL script file with command

When there are too many SQL commands (the SQL file is too large), the execution of PLSQL is slow and easy to timeout. At this time, you can directly execute the SQL script file with sqlplus command, as follows:

1. Sqlplus login

> sqlplus username/ [email protected]

2. Execute SQL file

If the SQL file is in the current directory, you can directly execute:

>@ demo.sql

If the SQL file is not in the current directory, you can use the absolute path:

>@ D:/demo.sql

[Solved] MySQL Error Code: 1093. You can’t specify target table ‘car’ for update in …

Error code: 1093. You can’t specify target table ‘car’ for update in from clause

 

Error code: 1093 error occurs when executing the following SQL statement:

update car set tag = 1 where id in (select id from car where brand_id=182 and tag=0);

 

The reason for the error is that the modified table and the queried table are the same table, which is not allowed in MySQL. We can solve the problem by querying again in the middle

update car set tag = 1 where id in (select id from (select id from car where brand_id=182 and tag=0) As temp);