I am far from being strong in MySQL or database, so I am using a tool called FlySpeed SQL Query. This tool helps me graphically created MySQL queries. Here is the query I got created with this tool and a lot of reading on the internet.
Select
Employee.Firstname As Prénom,
Employee.Name As NOM,
TimeSheet.Filled As Validé,
TimeSheet.Closed As Clôturé,
Sum(Imputation.Hours) As `Somme des heures`,
TimeSheet.Month + 1 As Mois,
TimeSheet.Year As Année
From
Employee Inner Join
TimeSheet On TimeSheet.Employee_Id = Employee.Id Inner Join
Imputation On Imputation.TimeSheet_Id = TimeSheet.Id Inner Join
Project On Imputation.Project_Id = Project.Id
Where
TimeSheet.Filled = '1' And
(TimeSheet.Closed = '0' Or
TimeSheet.Closed Is Null) And
Imputation.Day <= Last_Day(Current_Date - Interval 1 Month) And Imputation.Day >= Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01') And
Project.Id != '1'
Group By
Employee.Name, TimeSheet.Month + 1, TimeSheet.Year
Having
Sum(Imputation.Hours) >= 5 * ((DateDiff(Last_Day(Current_Date - Interval 1 Month), Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')) + 1))
Order By
Année,
Mois,
NOM
This query returns me exactly the desired result. Keeping the same conditions as the MySQL query above, I want to update the Closed field to "1". I would like to make some things that taste there:
-- UPDATE Query
--
UPDATE TimeSheet
SET Closed = '1'
--
-- UPDATE Query
From
Employee Inner Join
TimeSheet On TimeSheet.Employee_Id = Employee.Id Inner Join
Imputation On Imputation.TimeSheet_Id = TimeSheet.Id Inner Join
Project On Imputation.Project_Id = Project.Id
--
-- With those conditions
--
Where
TimeSheet.Filled = '1' And
(TimeSheet.Closed = '0' Or
TimeSheet.Closed Is Null) And
-- Calculating a time range
Imputation.Day <= Last_Day(Current_Date - Interval 1 Month) And Imputation.Day >= Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')
And
Project.Id != '1'
Group By
Employee.Name, TimeSheet.Month + 1, TimeSheet.Year
Having
-- Calculation : >= 5 times the number of days in the period
Sum(Imputation.Hours) >= 5 * ((DateDiff(Last_Day(Current_Date - Interval 1 Month), Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')) + 1))
---
-- With those conditions
So I need help to turn my SELECT query to an UPDATE query. Feel free to ask me for more information.
I have finally found the solution to my problem.
This article helped me a lot: http://www.codeproject.com/Tips/831164/MySQL-can-t-specify-target-table-for-update-in-FRO
Here it is:
UPDATE TimeSheet
SET
Closed = '1'
WHERE
TimeSheet.Id IN (SELECT
TimeSheet.Id
FROM
(SELECT
TimeSheet.Id
FROM
TimeSheet
Where
TimeSheet.Id IN (SELECT
TimeSheet.Id
FROM
Imputation
INNER JOIN TimeSheet ON Imputation.TimeSheet_Id = TimeSheet.Id
INNER JOIN Project ON Imputation.Project_Id = Project.Id
INNER JOIN Employee ON TimeSheet.Employee_Id = Employee.Id
Where
(TimeSheet.Closed = '0'
OR TimeSheet.Closed IS NULL)
AND TimeSheet.Filled = '1'
AND Imputation.Day <= Last_Day(Current_Date - INterval 1 Month)
AND Imputation.Day >= Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')
GROUP BY TimeSheet.Id
HAVING TimeSheet.Id NOT IN (SELECT DISTINCT
TimeSheet.Id
FROM
TimeSheet
INNER JOIN Imputation ON Imputation.TimeSheet_Id = TimeSheet.Id
INNER JOIN Project ON Imputation.Project_Id = Project.Id
INNER JOIN Employee ON TimeSheet.Employee_Id = Employee.Id
Where
Imputation.Day <= Last_Day(Current_Date - INterval 1 Month)
AND Imputation.Day >= Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')
AND Project.Id = '1')
AND Sum(Imputation.Hours) >= 5 * (DateDiff(Last_Day(Current_Date - INterval 1 Month), Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')) + 1))) VirtualTable01);
However the term "solution" is perhaps a bit strong. I would say more it is a workaround. So I'm not sure this request is well optimized and it meets the standards, but at least it has the advantage to give me the desired result.
Same query as above but commented:
-- UPDATE Query
--
UPDATE TimeSheet
SET
Closed = '1'
--
-- UPDATE Query
--
-- With those conditions
--
WHERE
-- First Select
--
TimeSheet.Id IN (SELECT
TimeSheet.Id
FROM
-- Second Select
--
(SELECT
TimeSheet.Id
FROM
TimeSheet
Where
-- Third Select
--
TimeSheet.Id IN (SELECT
TimeSheet.Id
FROM
Imputation
INNER JOIN TimeSheet ON Imputation.TimeSheet_Id = TimeSheet.Id
INNER JOIN Project ON Imputation.Project_Id = Project.Id
INNER JOIN Employee ON TimeSheet.Employee_Id = Employee.Id
Where
(TimeSheet.Closed = '0'
OR TimeSheet.Closed IS NULL)
AND TimeSheet.Filled = '1'
AND Imputation.Day <= Last_Day(Current_Date - INterval 1 Month)
AND Imputation.Day >= Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')
GROUP BY TimeSheet.Id
-- Fourth Select
--
HAVING TimeSheet.Id NOT IN (SELECT DISTINCT
TimeSheet.Id
FROM
TimeSheet
INNER JOIN Imputation ON Imputation.TimeSheet_Id = TimeSheet.Id
INNER JOIN Project ON Imputation.Project_Id = Project.Id
INNER JOIN Employee ON TimeSheet.Employee_Id = Employee.Id
Where
Imputation.Day <= Last_Day(Current_Date - INterval 1 Month)
AND Imputation.Day >= Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')
AND Project.Id = '1')
--
-- End Fourth Select
AND Sum(Imputation.Hours) >= 5 * (DateDiff(Last_Day(Current_Date - INterval 1 Month), Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')) + 1))
--
-- End Third Select
) VirtualTable01)
--
-- End Second Select
--
-- End First Select
;