#1
  1. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    222

    Is it possible to create a select update (or update select) query like this?


    I need to update a column based on a few JOINS, pretty tricky I guess.

    Column "minimum_number_of_nights" in allotments table needsto be updated based on the season of that day. The season of that day is decided by the join between the tables.

    Some relations:
    1. Each package has a specific value in allotments table for each day of the year (2012-01-01,...2012-12-12,..)
    2. Allotments table is connected to packages table using package_id
    3. Packages table is connected to hotels table with hotel_id
    4. Hotels table is connected to hotel_calendars with hotel_id

    Tables:

    Code:
    allotments table
    --------------------
    package_id
    day_date
    minimum_number_of_nights
    Code:
    packages table
    --------------------
    id
    low_season_minimum_number_of_nights
    high_season_minimum_number_of_nights
    shoulder_season_minimum_number_of_nights
    Code:
    hotels table
    --------------------
    hotel_id
    Code:
    hotel_calendars table
    --------------------
    hotel_id
    day_date
    season
    I thought I join them together like this but to update, I am not sure.

    Code:
    SELECT 
      packages.id AS package_id
    , calendars_new.day_date
    , hotels.hotelname 
    
    FROM packages
    
    INNER
      JOIN calendars_new
         ON calendars_new.hotel_id = hotels.hotel_id
    INNER
       JOIN hotels
         ON hotels.hotel_id = packages.hotel_id	
    WHERE 
    	calendars_new.day_date > '2012-07-04'
    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    could you please give some sample rows for the hotel_calendars table -- i want to see how the season works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    222
    Sure, here you go:
    Originally Posted by r937
    could you please give some sample rows for the hotel_calendars table -- i want to see how the season works
    Code:
    id            hotel_id                day_date      season
    
    5431432        4                      2012-07-09   low  
    5431433        4                      2012-07-10   high  
    5431434        4                      2012-07-11   low
    5431435        4                      2012-07-12   low
    5431436        4                      2012-07-13   shoulder

    *Note: Each day can be a different season
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    thanks

    you should change this --
    Code:
    packages table
    --------------------
    id
    low_season_minimum_number_of_nights
    high_season_minimum_number_of_nights
    shoulder_season_minimum_number_of_nights
    
    37  1 3 2
    to this --
    Code:
    packages table
    --------------------
    id
    season
    minimum_number_of_nights
    
    37 low      1
    37 high     3
    37 shoulder 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    222
    Sorry but I don't understand what you are saying.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by zxcvbnm
    Sorry but I don't understand what you are saying.
    your packages table is currently designed to hold three columns of minimum_number_of_nights on the same row, where the difference in what's in those three columns is determined by the name of the column

    i want you to change that so that there will be 3 rows instead, with the difference in what's in the minimum_number_of_nights column depending on the value in the season column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    222
    Originally Posted by r937
    your packages table is currently designed to hold three columns of minimum_number_of_nights on the same row, where the difference in what's in those three columns is determined by the name of the column

    i want you to change that so that there will be 3 rows instead, with the difference in what's in the minimum_number_of_nights column depending on the value in the season column
    I cant change table structures. There are thousands of hotels and members using this website as we speak!
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by zxcvbnm
    I cant change table structures. There are thousands of hotels and members using this website as we speak!
    too bad

    (and you shoulda mentioned that sooner)

    so now the sql will have to have a complex CASE expression that will add substantially to the clumsy factor

    (check out first normal form for future designs like this
    Code:
    UPDATE allotments
    INNER
      JOIN packages
        ON packages.id = allotments.package_id
    INNER
      JOIN hotel_calendars 
        ON hotel_calendars.hotel_id = packages.hotel_id
       AND hotel_calendars.day_date = packages.day_date
       SET allotments table.minimum_number_of_nights =
           CASE WHEN hotel_calendars = 'low'
                THEN low_season_minimum_number_of_nights
                WHEN hotel_calendars = 'high'
                THEN high_season_minimum_number_of_nights
                ELSE shoulder_season_minimum_number_of_nights
            END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    222
    Great I just learned CASE

    But in that query there are few things are wrong:

    AND hotel_calendars.day_date = packages.day_date

    Packages table does not have day_date

    Also I am not sure about the word table in SET allotments table.minimum_number_of_nights =

    Also this WHEN hotel_calendars = 'low' should be changed to WHEN hotel_calendars.season = 'low'

    Also this WHEN hotel_calendars = 'high' should be changed to WHEN hotel_calendars.season = 'high'

    Code:
    UPDATE allotments
    INNER
      JOIN packages
        ON packages.id = allotments.package_id
    INNER
      JOIN hotel_calendars 
        ON hotel_calendars.hotel_id = packages.hotel_id
       AND hotel_calendars.day_date = packages.day_date
       SET allotments table.minimum_number_of_nights =
           CASE WHEN hotel_calendars = 'low'
                THEN low_season_minimum_number_of_nights
                WHEN hotel_calendars = 'high'
                THEN high_season_minimum_number_of_nights
                ELSE shoulder_season_minimum_number_of_nights
            END
    So I changed it to this one below and it seems like it is working
    Code:
    UPDATE h2g.allotments
    INNER 
      JOIN h2g.packages
        ON h2g.packages.id = h2g.allotments.package_id
    INNER
      JOIN h2g.hotel_calendars
        ON h2g.hotel_calendars.hotel_id = h2g.packages.hotel_id
       AND h2g.hotel_calendars.day_date = h2g.allotments.day_date
       SET h2g.allotments.minimum_number_of_nights=
           CASE WHEN h2g.hotel_calendars.season = 'lo'
                THEN low_season_minimum_number_of_nights
                WHEN h2g.hotel_calendars.season = 'high'
                THEN high_season_minimum_number_of_nights
                ELSE shoulder_season_minimum_number_of_nights
            END

    Comments on this post

    • cafelatte disagrees : Now learn 'thank you' -a little documented function, but incredibly useful nontheless.
    Last edited by zxcvbnm; July 5th, 2012 at 11:49 PM.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    wow, what can i say... my sql was really sloppy and i made several mistakes, but you caught and corrected them

    nice one!

    Comments on this post

    • English Breakfast Tea agrees : Rudy, I have been employed for past 2 years cause of you and SimplySql, to show appreciations and keep cafe latte happy,, a BIG thank you to uncle Rudy. We love and respect you.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    222
    Originally Posted by r937
    wow, what can i say... my sql was really sloppy and i made several mistakes, but you caught and corrected them

    nice one!

    Also Rudy, the query has been running for past 60000 seconds and still going, I had never seen a query taking this long, I hope this is normal

IMN logo majestic logo threadwatch logo seochat tools logo