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

you can book me login
you can book me alternatives
how to create you can book me
you can book me vs calendly
ycbm link reference
youcanbook.me reviews
as.me booking website
free online appointment scheduling

I have a simple mysql table:

CREATE TABLE IF NOT EXISTS `pers` (
  `persID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) NOT NULL,
  `gehalt` int(11) NOT NULL,
  `chefID` int(11) DEFAULT NULL,
  PRIMARY KEY (`persID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES
(1, 'blb', 1000, 3),
(2, 'as', 1000, 3),
(3, 'chef', 1040, NULL);

I tried to run following update, but I get only the error 1093:

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE (P.chefID IS NOT NULL 
OR gehalt < 
(SELECT (
    SELECT MAX(gehalt * 1.05) 
    FROM pers MA 
    WHERE MA.chefID = MA.chefID) 
    AS _pers
))

I searched for the error and found from mysql following page http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html, but it doesn't help me.

What shall I do to correct the sql query?

The problem is that MySQL, for whatever inane reason, doesn't allow you to write queries like this:

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM myTable
    INNER JOIN ...
)

That is, if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)


The solution is to replace the instance of myTable in the sub-query with (SELECT * FROM myTable), like this

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)

This apparently causes the necessary fields to be implicitly copied into a temporary table, so it's allowed.

I found this solution here. A note from that article:

You don’t want to just SELECT * FROM table in the subquery in real life; I just wanted to keep the examples simple. In reality, you should only be selecting the columns you need in that innermost query, and adding a good WHERE clause to limit the results, too.

Can you pay the rent May 1? Here's what to do if you can't., On International Dance Day during a pandemic, we celebrate the toes as Idina Menzel asked us poor souls, "Don't you want to be the life of  Enjoy the videos and music you love, upload original content, and share it all with friends, family, and the world on YouTube.

You can make this in three steps:

CREATE TABLE test2 AS
SELECT PersId 
FROM pers p
WHERE (
  chefID IS NOT NULL 
  OR gehalt < (
    SELECT MAX (
      gehalt * 1.05
    )
    FROM pers MA
    WHERE MA.chefID = p.chefID
  )
)

...

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE PersId
IN (
  SELECT PersId
  FROM test2
)
DROP TABLE test2;

or

UPDATE Pers P, (
  SELECT PersId
  FROM pers p
  WHERE (
   chefID IS NOT NULL 
   OR gehalt < (
     SELECT MAX (
       gehalt * 1.05
     )
     FROM pers MA
     WHERE MA.chefID = p.chefID
   )
 )
) t
SET P.gehalt = P.gehalt * 1.05
WHERE p.PersId = t.PersId

International Dance Day: Top dancers are teaching us to dance at , We'll tell you what personal information you'll need to have on hand to make setup go more smoothly and explain what happens if you get an  You Can is a teen marijuana prevention campaign brought to you by the Washington State Department of Health

In Mysql, you can not update one table by subquery the same table.

You can separate the query in two parts, or do

 UPDATE TABLE_A AS A
 INNER JOIN TABLE_A AS B ON A.field1 = B.field1
 SET field2 = ? 

Do What You Can, Most of us have been working from home since March 11. We're here for the long haul. We remain committed to helping you understand the  YouCan مميزات . تمنحك منصة YouCan كل ما تحتاجه لإنشاء متجرك عبر الأنترنيت. منصة Youcan ليست فقط منصة لتصميم مواقع ويب، بل هي منصة متكاملة، توفر لك جميع الأدوات اللازمة لتبسيط عملك على الأنترنيت، حتى تنطلق إلى تطوير أدائك

Make a temporary table (tempP) from a subquery

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE P.persID IN (
    SELECT tempP.tempId
    FROM (
        SELECT persID as tempId
        FROM pers P
        WHERE
            P.chefID IS NOT NULL OR gehalt < 
                (SELECT (
                    SELECT MAX(gehalt * 1.05) 
                    FROM pers MA 
                    WHERE MA.chefID = MA.chefID) 
                    AS _pers
                )
    ) AS tempP
)

I've introduced a separate name (alias) and give a new name to 'persID' column for temporary table

Direct deposit should deliver your stimulus check faster. How to set it , If you are at higher risk for serious illness from COVID-19 because of your age or because you have a serious long-term health problem, it is extra important for  Simple online scheduling tool to eliminate the back and forth emails, with customer bookings straight into your calendar.

It's quite simple. For example, instead of writing:

INSERT INTO x (id, parent_id, code) VALUES (
    NULL,
    (SELECT id FROM x WHERE code='AAA'),
    'BBB'
);

you should write

INSERT INTO x (id, parent_id, code)
VALUES (
    NULL,
    (SELECT t.id FROM (SELECT id, code FROM x) t WHERE t.code='AAA'),
    'BBB'
);

or similar.

Coronavirus In LA: Your No-Panic Guide To Daily Life And The New , His story was the basis for the movie “Catch Me If You Can.” Argyle is already facing charges in four other criminal cases with several other  YOUCAN Youth Services is a non-profit charitable organization dedicated to assisting youth so they have the knowledge, support and skills to remove themselves from harm’s way.

What You Can Do, To ensure that you are ready for that conversation we've got some information to help you prepare. Servicers may have scripts that they use when  The song is You Can Be King Again by Lauren Aquilina. The anime is called Hotarubi no Mori e. I make no profit from this video, it is solely for entertainment purposes only. This AMV was inspired

Utah man compared to con artist in 'Catch Me If You Can' movie , 2019 Filers: We will send your payment using the information you provided with your 2019 tax return. You will not be able to change it. 2018 Filers  In just 5 minutes you can reset your day in a positive way. Special thanks to John Davisi for lending us his incredibly soothing voice. https://www.johndavis

Guide to coronavirus mortgage relief options, YOU CAN is completely reliant on the generosity of others. Help us to support some of the most disadvantaged young people in Greater Manchester. Our work has great success and a genuine impact on the lives of local people so please give us your support. You can donate via our local giving site.

Comments
  • Possible duplicate of MySQL Error 1093 - Can't specify target table for update in FROM clause
  • I don't think the reason is inane. Think about the semantics. Either MySQL has to keep a copy of the table before the update started, or the inner query might use data that has already been updated by the query as it's in progress. Neither of these side-effects is necessarily desirable, so the safest bet is to force you to specify what will happen using an extra table.
  • @siride: Other databases, such as MSSQL or Oracle, don't have this arbitrary restriction
  • @BlueRaja-DannyPflughoeft: it's not arbitrary. It's reasonable design decision based on the costs of the alternatives. The other DB systems chose to deal with those costs anyway. But those systems don't, e.g., let you include non-aggregated columns in SELECT lists when you use GROUP BY, and MySQL does. I'd argue that MySQL is in the wrong here, and I might say the same of the other DBMSs for UPDATE statements.
  • @siride From a relational algebra point of view, T and (SELECT * FROM T) are completely equivalent. They are the same relation. Therefore this is an arbitrary, inane restriction. More specifically, it's a workaround to coerce MySQL into doing something that it clearly can do, but for some reason it cannot parse in its simpler form.
  • In my case the accepted solution didn't work because my table was simply too large. The query never completed. Apparently this is taking too many internal resources. Instead I created a View with the inner query and used it for the data selection, which worked absolutely fine. DELETE FROM t WHERE tableID NOT IN (SELECT viewID FROM t_view); Also I recommend running OPTIMIZE TABLE t; afterwards to reduce the size of the table.
  • Well yeah, most subqueries can be rewritten as multiple steps with CREATE TABLE statements - I hope the author was aware of this. However, is this the only solution? Or can the query be rewritten with subqueries or joins? And why (not) do that?
  • I think you have a capitalization error in your second solution. Shouldn't UPDATE Pers P read UPDATE pers P?
  • Tried this solution and for a large number of entries in temporary/second table the query can be very slow; try to create temporary/second table with an index/primary key [see dev.mysql.com/doc/refman/5.1/en/create-table-select.html ]
  • As @Konerak states, this isn't really the best answer. The answer from BlueRaja below seems best to me. Upvotes seem to agree.
  • @Konerak, Doesn't CREATE TABLE AS SELECT give horrible performance?
  • SELECT ... SET? I've never heard about this.
  • @grisson Thanks for the clarification. Now I get why my IN clause doesn't work - I was targeting the same table.