Tag Archives: SQL

sql Error: Every derived table must have its own alias [How to Solve]

Mysql database is used

 1 UPDATE tb_cr_circulate_gtsc 
 2 set path_status='A06'
 3 where
 4 order_id='Ph800601211123155507'
 5 and
 6 receive_role='21075116-001'
 7 and
 8 receive_role_name='024722cd-b197-462f-bdc2-f0423d88e580&Z&11caea25-4996-4682-aa3c-ea698e5140cc&21075116-001&四川省川东农药化工有限公司'
 9 and 
10 ( 
11 (SELECT statusSum from   (select COUNT(path_status)  as statusSum FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' ) )
12 <  
13 (SELECT statusSum from  (select COUNT(path_status)  as statusSum  FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') )
14 )

Error message: each derived table must have its own alias

Solution: add an alias to the newly generated table

1 ( 
2 (SELECT statusSum from   (select COUNT(path_status)  as statusSum FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' ) as a)
3 <  
4 (SELECT statusSum from  (select COUNT(path_status)  as statusSum  FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') as a )
5 )

[Solved] sql Error: You can’t specify target table ‘tb_cr_circulate_gtsc’ for update in FROM clause

Mysql database is used

The SQL statement is as follows

 1 UPDATE tb_cr_circulate_gtsc 
 2 set path_status='A06'
 3 where
 4 order_id='Ph800601211123155507'
 5 and
 6 receive_role='21075116-001'
 7 and
 8 receive_role_name='024722cd-b197-462f-bdc2-f0423d88e580&Z&11caea25-4996-4682-aa3c-ea698e5140cc&21075116-001&四川省川东农药化工有限公司'
 9 and 
10 ((select COUNT(path_status)   FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' )
< (select COUNT(path_status) FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') )

According to the online query: you cannot select some values of the same table in the same SQL statement, and then update the table.

Solution: select multiple times through an intermediate table

1 ( 
2 (SELECT statusSum from   (select COUNT(path_status)  as statusSum FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' ) )
3 <  
4 (SELECT statusSum from  (select COUNT(path_status)  as statusSum  FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') )
5 )

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/ password@dbname

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