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.
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