mysql - How to insert or update based on some condition? -

can please me this! have 2 tables:

1.existing pricing table custom date-dependent pricing windows:

  id | prod id |   start    |    stop    |   price   -----+---------+------------+------------+-----------    1 |   65210 | 2012-01-01 | 2013-01-01 |  5.00         2 |   54841 | 2012-02-05 | 2013-03-01 | 15.00         3 |   51518 | 2012-01-01 | 2013-01-01 |  5.00         4 |    8402 | 2012-01-01 | 2017-01-01 |  5.00         5 |    1520 | 2012-01-01 | 2050-01-01 | 12.00         6 |     959 | 2013-10-01 | 2018-01-01 |  5.00   

2."new" pricing data table (used update table above each unique prod id):

 prod id |   start    |    stop    |   price      -------+------------+------------+-----------    65210 | 2013-01-01 | 2025-01-01 |  5.00         54841 | 2013-02-05 | 2017-03-01 | 15.00           959 | 2013-01-01 | 2017-01-01 |  5.00     

what's best way update stop date in table 1 stop date in table 2 assuming it's still "in window"? if existing stop date before "new" start date, new pricing record created new start , stop dates.

thanks!

...to update stop date in table 1 stop date in table 2 assuming it's still "in window"...

update price p join new_price n     on p.prod_id = n.prod_id    , n.start between p.start , p.stop    set p.stop = n.stop 

to insert new record "...if existing stop date before new start date..."

insert price (`prod_id`, `start`, `stop`, `price`) select n.prod_id, n.start, n.stop, n.price   new_price n join price p     on n.prod_id = p.prod_id    , n.start > p.stop 

here sqlfiddle demo