CODEWITHSUNDEEP
mysqlsqlsql-update
Stan
Stan

Reputation: 107

UPDATE + SET + WHERE - Dynamic minimum value

This is a follow-up to: Dynamic minimum value for specfic range (mysql)

I do have the query to fetch the third column (lowest of the last 3 days) "Low_3_days" via SELECT command:

-----------------------------------------
| Date         | Unit_ | Lowest_in_last_|
|              | price |  3_days        | 
|----------------------------------------
| 2015-01-01   | 15    |      15        | 
| 2015-01-02   | 17    |      15        | 
| 2015-01-03   | 21    |      15        | 
| 2015-01-04   | 18    |      17        | 
| 2015-01-05   | 12    |      12        | 
| 2015-01-06   | 14    |      12        |
| 2015-01-07   | 16    |      12        | 
|----------------------------------------

select S.Date,Unit_price, 
    (select S.Date, Unit_price, 
        (SELECT min(s2.Unit_Price) 
         FROM table s2
         WHERE s2.DATE BETWEEN s.DATE - interval 3 day and
                              s.DATE - interval 1 day
        ) as min_price_3_days
FROM table S;

My new challenge is - what is the best way to use UPDATE-SET-WHERE so I could add the ("Lowest_in_last_3_days") values to a new column in a table (instead of having temporary results displayed to me via SELECT).

By following the UPDATE-SET-WHERE syntax, the query would be:

UPDATE table 
    SET min_price_3_days = 
         (select S.Date, Unit_price, 
       (SELECT min(s2.Unit_Price) 
        FROM table s2
        WHERE s2.DATE BETWEEN s.DATE - interval 3 day and
                              s.DATE - interval 1 day
       ) as min_price_3_days

but I have difficulties constructing the correct query. What would be the correct approach to this? I do recognize this one is a tough one to solve.

Upvotes: 3

Views: 272

Answers (1)

rtruszk
rtruszk

Reputation: 3922

Your UPDATE should look like:

update table set low_3_days=
    (SELECT min(Unit_Price) 
     FROM (select unit_price, date as date2 from table) as s2
     WHERE s2.date2 BETWEEN date - interval 3 day and date - interval 1 day     
);

You can check it in SQLFiddle

In Fiddle I used different names for table and column. I prefer not to use SQL keywords as names

Upvotes: 1

Related Questions