Stack Overflow
Stack Overflow

Questions tagged [mysql-error-1093]

ERROR 1093 (HY000): You can't specify target table '%s' for update in FROM clause

ERROR 1093 (HY000): You can't specify target table '%s' for update in FROM clause

Explanation

The MySQL UPDATE statement doesn't allow subqueries against the same table that is being updated. IE: The following will fail with the 1093 error message:

UPDATE TABLE_A
 WHERE col IN (SELECT col2
                 FROM TABLE_A)

...or:

UPDATE TABLE_A
   SET ...
 WHERE col = (SELECT col2
                 FROM TABLE_A)

Solutions

There are two means of solving this issue:

  • MySQL specific - Use the ANSI-92 JOIN syntax:

    UPDATE TABLE_A a
  JOIN TABLE_A b ON b.col2 = a.col
   SET ...

  • Bury the subquery one level deeper

    UPDATE TABLE_A
   SET ...
 WHERE col = (SELECT x.col 
                FROM (SELECT col2 AS col
                        FROM TABLE_A) AS x)
84 questions
1
vote
1 answer

How to select complex data from a table and delete it from the same table

I have a feedback table which contains user feedbacks and ratings as per some entity. There are cases in which multiple users have voted on the same entity which is not desirable and have entered the system due to a glitch. The table schema is…
Mono Jamoon
  • 3,967
  • 15
  • 36
  • 59
1
vote
1 answer

error 1093: MySQL can’t specify target table for update in FROM clause

error 1093: MySQL can’t specify target table 'SENTIERO' for update in FROM clause This is my trigger: CREATE TRIGGER lunghezza_sentiero_datoderivato_INSERT AFTER INSERT ON SENTIERO_HA_TAPPA FOR EACH ROW BEGIN UPDATE SENTIERO SET lunghezza= (SELECT…
FrancescoN
  • 1,766
  • 7
  • 28
  • 40
1
vote
1 answer

Using the result of subquery to update a table also referred to in the subquery

I'm trying to fix an incomplete database where I don't have control over the schema and I need to find missing entries and insert them. This is syntax I've arrived at: INSERT INTO downloads (product_id, filename) VALUES ( products_id = (…
Mark Fox
  • 8,052
  • 9
  • 48
  • 72
1
vote
1 answer

You can't specify target table 'barang' for update in FROM clause

I have problem updating column jml_tersedia from table barang. The value should be: barang.jml_tersedia = pembelian.jml_beli - penjualan.jml_jual. Here is my code: update barang set jml_tersedia = ( ( select ifnull(sum(jml_beli), 0) from…
Dewanta
  • 188
  • 8
1
vote
2 answers

ERROR 1093 (HY000): You can't specify target table for update in FROM clause

I tried the following query: mysql> INSERT INTO tm_visitor (VDT,VID, NAME, CONTACT) values(REPLACE(LE FT(NOW(), 10), '-', ''),(SELECT LPAD(COALESCE(MAX(VID) + 1, 000001), 6, '0') FRO M tm_visitor),'yatin','9876543120'); but I get the following…
yatinbc
  • 515
  • 3
  • 13
  • 32
0
votes
3 answers

Update Value in Table from another table

I realized that i was using a varchar attribute as a index/key in a query, and that is killing my query performance. I am trying to look in my precienct table and get the integer ID, and then update my record in the household table with the new int…
gsueagle2008
  • 4,171
  • 8
  • 34
  • 46
0
votes
1 answer

MySQL query error

I have a problem with a sql query is the following, I need to delete some data, but referenced parents and children of these records, for example: mysql> select * from…
user862010
0
votes
3 answers

Update subsequent duplicate field values in mysql

I have the following schema: id | order_ref | description | price Currently I have the following duplicate issue: 1 | 34567 | This is the description | 19.99 2 | 34567 | This is the description | 13.99 This was due to the data I was importing…
Jeepstone
  • 2,613
  • 5
  • 20
  • 38
0
votes
2 answers

MySQL Error 1093 - Can't specify target table for update in FROM clause (two joins)

I'm trying to update the unit_price in a sales_order_parts detail table with calculation from the applied_discount field from sales_orders. Also, the price is in the master_part_list table as price. When I run from the select statement down, it…
Paul Santoro
  • 13
  • 2
0
votes
2 answers

Insert into MySQL a huge number of rows, based on subquery... having trouble

So, what I am trying to do is insert a row of NONE, $country for every country that exists in the table. It should look like Afghanistan, NONE Albania, NONE Andorra, None ... That is, in addition to the provinces listed for each country...…
Ryan Ward
  • 5,764
  • 6
  • 35
  • 48
0
votes
0 answers

error 1093, but there is no selection in the subquery

I´m creating a simple procedure to delete a row from the table tbGame, but when I try to call the proc it returns the error 1093 I have tried to figure out what's happening by deleting the row using the normal command(DELETE FROM ) but it…
pipoca_com_sazom
  • 1
0
votes
1 answer

Mysql - You can't specify target table for update in FROM clause - Subquery misunderstanding?

I am stuck doing a project for school atm. and the teachers barely explained SQL to us. So I have a simple table: CREATE TABLE `VINOVOLUPTATEM`.`TimeWorked` ( `TimeID` INT NOT NULL AUTO_INCREMENT, `MNr` INT NOT NULL, `Start` TIMESTAMP…
Kazzed
  • 3
  • 2
0
votes
3 answers

Mysql update subquery specify target table

I'm having trouble with updating final_id by selecting the highest final_id already in table and adding +1. The query below outputs the error: "You can't specify target table 'customer_orders' for update in FROM clause" and I sadly fail to see…
Kristian
  • 1,263
  • 4
  • 27
  • 45
0
votes
2 answers

Error referencing main table on lookup subquery for update

First of all, this question is not answered earlier since the thread about error 1093 shows a simple subquery. In my case, I'm doing a lookup for the next record referencing the main table. Please don't tag it as a duplicate without first reading…
Martin Ocando
  • 896
  • 2
  • 8
  • 16
0
votes
1 answer

Can't update table with the same column from next record Error 1093

I need to update the records of a table which has a wrong date (1970-01-01), using the next record's data (according to the incremental gkey field). So, if I do this query: SELECT aa.gkey, aa.course_date, (select course_date from…
Martin Ocando
  • 896
  • 2
  • 8
  • 16
1 2 3
4
5 6