1

I have a table flaggedcomment and a table price.

In flaggedcomment, there are two empty columns Price_DateTime and Price_Open which I want to copy from price, depending on the ON conditions. Query as below.

UPDATE flaggedcomment as t1 
        LEFT JOIN price as t2 
             ON t1.Ticker_Ticker_ID = t2.Ticker_Ticker_ID AND t1.Comment_DateTime = t2.Price_DateTime
SET t1.Price_DateTime = t2.Price_DateTime, t1.Price_Open = t2.Price_Open

This query will leave some cells with null value because there's no match between t1.Comment_DateTime and t2.Price_DateTime.

Is there a way to "pick" the next available Price_DateTime if there's no match found?

For example, if the value of t1.Comment_DateTime is 2014-09-29 20:44:00 but it's not exist in t2.Price_DateTime, can I pick the next row's Price_DateTime value, e.g. 2014-09-30 08:00:00?

I am new to deal with MySQL queries and I've tried my best in explaining what I want to do. If I didn't explain my question well, please let me know and I will try to explain it further.

Any help would be much appreciated. Thank you!

2

I assume you mean you want to find the "first" row with same Ticker_ID and same or bigger DateTime, first when ordered by DateTime ascending. One way to do this, assuming that the price table has a unique constraint on (Ticker_Ticker_ID, Price_DateTime):

UPDATE flaggedcomment AS fc 
    INNER JOIN price AS p
        ON  p.Ticker_Ticker_ID = fc.Ticker_Ticker_ID 
        AND p.Price_DateTime = 
            ( SELECT pi.Price_DateTime
              FROM price AS pi
              WHERE pi.Ticker_Ticker_ID = fc.Ticker_Ticker_ID 
                AND pi.Price_DateTime >= fc.Comment_DateTime
              ORDER BY pi.Price_DateTime ASC
              LIMIT 1
            ) 
SET
    fc.Price_DateTime = p.Price_DateTime, 
    fc.Price_Open = p.Price_Open ;
  • Hi ypercube, thank you for your help! this does the trick! :D – Shyuan Jan 27 '15 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.