#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,572
    Rep Power
    171

    SELECT UPDATE if the row does not exist.


    Hello I need help to finish a query. I have done most of it and it is working fine, I just need to finish it.

    Basically it is a "SELECT, if record not exist, UPDATE" query. I made some visual to make it very easy to understand. Basically if the values for ALLOTMENTS_DAY_DATE is NULL, I want to insert the required values. I have written the select part of the query and it works fine. I am successfully retrieving the values I need to insert (in case necessary). This is the select query
    Code:
     SELECT packages.id              AS PACKAGE_ID,
           packages.hotel_id        AS HOTEL_ID,
           packages.room_id         AS ROOM_ID,
           annual_calendar.day_date AS DAY_DATE,
           allotments_new.day_date  AS ALLOTMENTS_DAY_DATE,
           packages.low_min_nights  AS PACKAGE_LOW_MIN_NIGHTS,
           packages.low             AS PACKAGE_LOW_RATE,
           hotels.beta_supplier     AS SUPPLIER_ID
    
    FROM   packages
    
           INNER JOIN hotels
                   ON hotels.hotel_id = packages.hotel_id
                      AND hotels.status = 'active'
    
    
           INNER JOIN hotelrooms
                   ON hotelrooms.room_id = packages.room_id
                      AND hotelrooms.status = 'active'
    
    
           LEFT OUTER JOIN calendars_new
                        ON calendars_new.hotel_id = hotels.hotel_id
                           AND calendars_new.day_date <= :next_year
                           AND calendars_new.day_date >= :today
    
    
           LEFT OUTER JOIN allotments_new
                        ON allotments_new.package_id = packages.id
                           AND allotments_new.day_date <= :next_year
                           AND allotments_new.day_date >= :today
                           AND calendars_new.day_date = allotments_new.day_date
    
    
           LEFT OUTER JOIN annual_calendar
                        ON annual_calendar.day_date = calendars_new.day_date
                           AND annual_calendar.day_date <= :next_year
                           AND annual_calendar.day_date >= :today
    
    WHERE  packages.status = 'active'
    and this is the insert query I need to integrate:
    Code:
     INSERT INTO allotments_new
                (package_id,
                 room_id,
                 hotel_id,
                 date_added,
                 day_date,
                 day_rate,
                 number_of_rooms,
                 status,
                 supplier_id,
                 day_of_the_week)
    VALUES      ('".$row['PACKAGE_ID']."',
                 '".$row['ROOM_ID']."',
                 '".$row['HOTEL_ID']."',
                 '".date('Y-m-d')."',
                 '$row['DAY_DATE']',
                 '$row['PACKAGE_LOW_RATE']',
                 0,
                 'b',
                 '".$row['SUPPLIER_ID']."',
                 '".strtolower(date('l',strtotime($row['DAY_DATE']) ))."' )
    Many thanks.
  2. #2
  3. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,749
    Rep Power
    9397
    First a question. Can you write the SELECT so that it pulls the data you want to insert? All the columns and all the values exactly as you want it to go into the table.
    * For literal values you can just SELECT "literal string", 123
    * NOW() will get you the current date/time
    * LOWER() and DATE_FORMAT() can take the place of the strtolower() and date()+strtotime() you have now

    Once you have the SELECT as you want, turn it into an INSERT...SELECT
    Code:
    INSERT INTO table (fields) SELECT ...
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,572
    Rep Power
    171
    Originally Posted by requinix
    First a question. Can you write the SELECT so that it pulls the data you want to insert? All the columns and all the values exactly as you want it to go into the table.
    Hello, thank you. Yes. The query above does that.
    Originally Posted by requinix
    * For literal values you can just SELECT "literal string", 123
    * NOW() will get you the current date/time
    * LOWER() and DATE_FORMAT() can take the place of the strtolower() and date()+strtotime() you have now
    Once you have the SELECT as you want, turn it into an INSERT...SELECT
    Code:
    INSERT INTO table (fields) SELECT ...
    I retrieve all the values. I understand the concept, writing it down as a query is very difficult. Specially the if part.
    Last edited by zxcvbnm; December 12th, 2012 at 10:35 PM.
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,572
    Rep Power
    171
    Originally Posted by requinix
    First a question. Can you write the SELECT so that it pulls the data you want to insert? All the columns and all the values exactly as you want it to go into the table.
    * For literal values you can just SELECT "literal string", 123
    * NOW() will get you the current date/time
    * LOWER() and DATE_FORMAT() can take the place of the strtolower() and date()+strtotime() you have now

    Once you have the SELECT as you want, turn it into an INSERT...SELECT
    Code:
    INSERT INTO table (fields) SELECT ...
    How can I insert? Where in the query? I made a
    visual help. If you scroll down from row 1860 you see the yellow text saying record not found. Those are the records I need to insert.

    I think I have to write the query like this below but I need some help to write this query
    Code:
    INSERT INTO table () VALUES ()
    WHERE SELECT columns FROM table WHERE id = NULL
  8. #5
  9. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,749
    Rep Power
    9397

    copy/paste from a PM


    Add a WHERE with allotments_new.day_date IS NULL: that should get you only the yellow rows. Then make sure you're selecting only the columns whose data you need (package_id, room_id, ... supplier_id, day_of_the_week) and stick an "INSERT INTO allotments_new (columns...)" in front of the SELECT.

    So I think something like
    Code:
    INSERT INTO allotments_new (package_id, room_id, hotel_id, date_added, day_date,
    	day_rate, number_of_rooms, status, supplier_id, day_of_the_week)
    SELECT
    	packages.id,
    	packages.room_id,
    	packages.hotel_id,
    	NOW(),
    	annual_calendar.day_date,
    	/* number_of_rooms */,
    	/* status */,
    	hotels.beta_supplier,
    	/* day_of_the_week */
    FROM packages
    INNER JOIN hotels ON
    	hotels.hotel_id = packages.hotel_id
    	AND hotels.status = 'active'
    INNER JOIN hotelrooms ON
    	hotelrooms.room_id = packages.room_id
    	AND hotelrooms.status = 'active'
    LEFT OUTER JOIN calendars_new ON
    	calendars_new.hotel_id = hotels.hotel_id
    	AND calendars_new.day_date <= '2014-02-07'
    	AND calendars_new.day_date >= '2013-02-07'
    LEFT OUTER JOIN allotments_new ON
    	allotments_new.package_id = packages.id
    	AND allotments_new.day_date <= '2014-02-07'
    	AND allotments_new.day_date >= '2013-02-07'
    	AND calendars_new.day_date = allotments_new.day_date
    LEFT OUTER JOIN annual_calendar ON
    	annual_calendar.day_date = calendars_new.day_date
    	AND annual_calendar.day_date <= '2014-02-07'
    	AND annual_calendar.day_date >= '2013-02-07'
    WHERE
    	packages.status = 'active'
    	AND packages.hotel_id = 4
    	AND allotments_new.day_date IS NULL
    Make sure you're not getting duplicate rows back: cut out any table references you don't need, and if you can't think of anything else to do make it a SELECT DISTINCT (the performance will drop but it'll work).

    Also, you can use BETWEEN...AND in place of a >= and <=, like
    Code:
    calendars_new.day_date BETWEEN '2013-02-07' AND '2014-02-07'
    Side thought: if number_of_rooms comes from a COUNT on the hotelrooms table you can add in a GROUP BY just like normal.

IMN logo majestic logo threadwatch logo seochat tools logo