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

Question

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 NOT NULL,
    `End` TIMESTAMP NULL,
    `TotalTime` DOUBLE NULL,

    PRIMARY KEY (`TimeID`),
    FOREIGN KEY (MNr) REFERENCES employees (MNr)
);

The table is meant to track working times of all the employees.

If I try to enter data into the table using this:

INSERT INTO TimeWorked (MNr, Start, End, TotalTime)
VALUES (0002,'2019-06-30 08:30:00', '2019-06-30 17:00:00', '8.5');

It works perfectly fine, but when I try to automate the "TotalTime" using this:

INSERT INTO TimeWorked (MNr, Start, End, TotalTime)
VALUES (0001,'2019-06-30 15:00:00', '2019-06-30 18:30:00', 
(TIMESTAMPDIFF(HOUR,(SELECT Start FROM TimeWorked WHERE TimeID = (SELECT MAX(TimeID) FROM TimeWorked)) , 
(SELECT End FROM (SELECT End FROM TimeWorked WHERE TimeID = (SELECT MAX(TimeID) FROM TimeWorked)) AS End)
)));

I keep getting an error:

Error Code: 1093. You can't specify target table 'arbeitszeit' for update in FROM clause

I already looked at different posts here and it seems to be related to the subquery restrictions by MySQL. Yet I can't find my error here.

What do I have to do to correct the query?

Note: I translated parts of the names to English to make it easier. Names in the real table to not represent any statements.

You should not be getting that error on an `insert`. Do you have a trigger on the table? — Gordon Linoff, Jul 05 '19 at 18:52
@Gordon Linoff Not entirely sure how triggers work, but i looked it up and i did not specify any. Except for the code shown here the is no other code linked to this table. — Kazzed, Jul 05 '19 at 19:11
Where is the `arbeitszeit` table in your query? Is that the real name of `TimeWorked`? — Barmar, Jul 05 '19 at 19:20
@Kazzed: if your concern is to have the time calculated automatically from start and end, you could just insert a dummy value on creation and then run an update like update TimeWorked set TotalTime=TIMESTAMPDIFF(HOUR, end, start) or something like this — jottbe, Jul 05 '19 at 19:36
It should also be possible to use it in the insert statement and fill in the timestamp values I guess. You could consider generating your insert script btw. I sometimes do such things with excel, but you can also do it in a programming language (i can say that it is quite easy with python). — jottbe, Jul 05 '19 at 19:38

Barmar · Accepted Answer · 2019-07-05T19:47:32.663

2

Don't put the SELECT inside the VALUES list, use INSERT ... SELECT:

INSERT INTO TimeWorked (MNr, Start, End, TotalTime)
SELECT 0001,'2019-06-30 15:00:00', '2019-06-30 18:30:00', TIMESTAMPDIFF(HOUR, Start, End)
FROM TimeWorked
ORDER BY TimeId DESC
LIMIT 1

This is similar to using a JOIN instead of a subquery in the WHERE clause when trying to do UPDATE or DELETE, as in

MySQL Error 1093 - Can't specify target table for update in FROM clause

edited Jul 05 '19 at 19:47

answered Jul 05 '19 at 19:26

Barmar

741,623
53
500
612

It's the same error that you get if you try to use a subquery that refers to the same table in an `UPDATE` or `DELETE` query. Why do you think it's a bug in `INSERT`? – Barmar Jul 05 '19 at 19:45
. . . It is a peculiarity of `values`. As your query shows, you can have the same table in the `select` of an `insert`. – Gordon Linoff Jul 05 '19 at 19:48
Of course, just as it's a peculiarity of `WHERE` in `UPDATE/DELETE` queries, but you can refer to the same table with `JOIN`. – Barmar Jul 05 '19 at 19:49
You've been using MySQL long enough to know that it's full of silly restrictions like this, like not being able to refer to the same temporary table twice in a query. – Barmar Jul 05 '19 at 19:50

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

1 Answers1