Im trying to apply this solution to my data in table in MySQL DB which has duplicates. And I get such error:
Also tried too assign aliases - but without success.What reason of this error here? It generally points that SQL script can produce cyclic process, but here I actually dont see any relevant to this - It is obvious that two selections for
DELETE and for SELECT is detached - engine must do SELECT once firstly and then use it in WHERE conditions for DELETE .So - why this error happens and how can I actually deduplicate my table? =)
Community♦
Gill BatesGill Bates
2,3961414 gold badges4949 silver badges105105 bronze badges
5 AnswersTechie
30.3k3434 gold badges132132 silver badges213213 bronze badges
echo_Meecho_Me
33.3k55 gold badges4242 silver badges7171 bronze badges
Your query is correct and would work on other DBMS, but MySQL doesn't allow you to update or delete from a table and select from the same table in a subquery. It is documented on the official DELETE docs.
It might be fixed on future releases, but currently your query is not supported.
A simple fix would be to put your subquery in a sub-subquery, like in echo_Me answer:
this will force MySQL to create a temporary table with the results of your subquery, and since you actually are not selecting from the same table, but from a temporary table, this query will run fine. However, performances might be poor.
You usually get rid of error #1093 by using joins. This is your query in join form:
or you can use this simpler version, which should be the fastest one:
Fiddle is here.
fthiellafthiella
40.6k1313 gold badges6868 silver badges9393 bronze badges
From the manual:
'Currently, you cannot delete from a table and select from the same table in a subquery.'
Use a proper database engine, or run the queries separately. (Storing the id's programmatically, in between.)
aibaib
34.7k1010 gold badges6464 silver badges7474 bronze badges
If you want to delete records from a table use
DELETE FROM tablename . You cannot specify a column here. If you want to remove the data from a column please use UPDATE statement to set the value of the column to blank or NULL.
Biju PBiju P
the sintax of delete
Rollercoaster tycoon download full version free. Dec 21, 2018 - Roller Coaster Tycoon 2. Download 117,48 MB. Build and manage'. A review by Andrew. If you want to have a unique genre in the game g. Roller Coaster Tycoon 2: Triple Thrill Pack, free and safe download. Roller Coaster Tycoon 2: Triple Thrill Pack latest version: The best Roller Coaster Tycoon.
not require the name of columns
because mysql delete all data that meet a condition
bomberdinibomberdini
Not the answer you're looking for? Browse other questions tagged mysqlsql or ask your own question.
I have got MySQL error 'You can't specify target table 'tasks' for update in FROM clause' running the following query:
How can I manage this?
Thanx!
OMG Ponies
266k6363 gold badges452452 silver badges471471 bronze badges
ohavrylohavryl
12111 gold badge22 silver badges1414 bronze badges
2 Answers
You can wrap it in a subquery like so. The issue is that MySQL can't update rows that it's also querying. This will make MySQL use a temporary table implicitly to store the ids you want to delete.
NovikovNovikov
3,61822 gold badges1919 silver badges3333 bronze badges
It's because you're specifying the tasks table more than once. Try:
Denis de BernardyDenis de Bernardy
59.1k88 gold badges9393 silver badges124124 bronze badges
Not the answer you're looking for? Browse other questions tagged mysqlsqlmysql-error-1093 or ask your own question.
Nonton saint seiya hades chapter sanctuary sub indo. I have a table employees in my database with corrupt entries.
I tried to delete them excuting:
but I get the next error:
#1093 - You can't specify target table 'pracownicy' for update in FROM clause
How can I overcome this?
BalgoreBalgore
2 Answers
In MySQL you can't delete from a row where you are selecting from at the same time. To overcome this you can either use another subquery to hide this fact or you can turn this into a join like this
juergen djuergen d
164k2626 gold badges219219 silver badges280280 bronze badges
You Can't Specify Target Table 'test For Update In From Clause California
BalgoreBalgore
Not the answer you're looking for? Browse other questions tagged mysqlsubquerymysql-error-1093 or ask your own question.
I am trying to update MySQL row.
my query is
and I am getting this error message: You can't specify target table 'x' for update in FROM clause
can anybody help me with this issue?
I am using MySQL version 5.6.38.
i saw this You can't specify target table for update in FROM clause answer but i could not figure this out.
habibunhabibun
98811 gold badge1010 silver badges2121 bronze badges
1 AnswerGordon LinoffGordon Linoff
827k3838 gold badges338338 silver badges443443 bronze badges
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Not the answer you're looking for? Browse other questions tagged mysqlsql or ask your own question.
You can't specify target table for update in FROM clause
Posted by: Douglass Davis Date: June 22, 2014 02:15AM
How can I do the following in a single query: UPDATE acx_project_users u_invited SET user_id = 38 WHERE user_id = 5514 AND NOT EXISTS ( SELECT 1 FROM acx_project_users u_existing WHERE u_existing.user_id = u_invited.user_id AND u_existing.project_id = u_invited.project_id ) The above query gives me an error of Error Code: 1093. You can't specify target table 'u_invited' for update in FROM clause Basically all I want to do is change from one user id to another, but not if there is already a row with that user id and project id
Options:Reply•Quote
You Can't Specify Target Table 'test' For Update In From Clause
Written By
You can't specify target table for update in FROM clause
You Can't Specify Target Table 'test For Update In From Clause Free
June 22, 2014 02:15AM
Re: You can't specify target table for update in FROM clause
June 24, 2014 07:45AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |