[SOLVED] Return DATE when year, week number and weekday given in one mysqli query

Return DATE when year, week number and weekday given in one mysqli query

I have the following table :

--------------------------------------
| Year |  Week  |  Weekday |   Date  |
--------------------------------------
| 2019 |   20   |     3    |         |
| 2019 |   10   |     4    |         |
| 2019 |    2   |     1    |         |
| 2019 |   41   |     2    |         |
--------------------------------------

I would like the last column to be filled with the exact date ("Y-M-d"), based on year, week and weekday from the other columns. Is it possible to do this in one MYSQLi query ? So far, I tried:

UPDATE table 
SET Date = STR_TO_DATE(Year Week Weekday, '%X %V %w')

AND

UPDATE table 
SET Date = DATE(STR_TO_DATE(Year Week Weekday, '%X %V %w'))

AND

UPDATE table 
SET Date = DATE(DATE_FORMAT(Year Week Weekday, '%Y %m %d'))

Without succes. Any ideas on how to get this in one query ?

Solution

With MAKEDATE() you can calculate the date like this:

UPDATE tablename 
SET Date = MAKEDATE(Year, (Week - 1) * 7 + Weekday);

See the demo.
Results:

| Year | Week | Weekday | Date       |
| ---- | ---- | ------- | ---------- |
| 2019 | 20   | 3       | 2019-05-16 |
| 2019 | 10   | 4       | 2019-03-08 |
| 2019 | 2    | 1       | 2019-01-08 |
| 2019 | 41   | 2       | 2019-10-09 |