Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    8

    Unanswered: UPDATES and JOINS trouble

    Right, I have two tables

    Flight (FlightID, FlightDate, Origin, Destination, MaxCapacity, AvailableSeats)

    and..

    FlightBooking (BookingID, CustomerID, FlightID, NumSeats, Status, BookingTime)

    Basically once the status in FlightBooking has been changed to 'Cancelled' I want to update flight by adding on the NumSeats in FlightBooking to the AvailableSeats in Flight, to simulate seats being freed up once a flighbooking has been cancelled. This has to be done with only one input 'BookingID'

    changing the status seems easy enough using

    Code:
    UPDATE FlightBooking SET Status = 'C' WHERE BookingID="+bookingID+";
    but updating the seats seems harder as you are only given BookingID

    The unfinished SQL for this statemet I have so far is...

    Code:
    UPDATE Flight SET AvailableSeats = ((SELECT NumSeats FROM FlightBooking WHERE BookingID="+bookingID+") + (SELECT AvailableSeats FROM Flight JOIN FlightBooking ON Flight.FlightID = FlightBooking.FlightID WHERE BookingID="+bookingID+")) WHERE............;
    A little explantion...

    You want to update the available seats in the flight table once the booking has been cancelled, so you SET this to the Number of seats from the cancelled booking + the current available seats in Flight = new amount available seats. As you can see my SQL is very messy and probably incorrect.

    I figured you need to join the tables with the common FlightID column. No idea what to put in the WHERE statement as we need to update Flight but have no inputs from that table.

    simply put, I'm struggling to JOIN and UPDATE.

    Hope that makes some sense.........

    ta,
    Last edited by Konnor; 03-13-06 at 08:54.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in practical terms, standard sql is not what you want, because joined update syntax varies too much from one database system to the next

    please indicate which database system you're using, so we can move this thread to the appropriate forum to get you an accurate answer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    8
    it's DB2, ta

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Here's what I would do:
    Code:
    UPDATE Flight
    SET AvailableSeats = AvailableSeats 
                       + (SELECT NumSeats FROM FlightBooking WHERE BookingID="+bookingID+")
    WHERE FlightID = (SELECT FlightID FROM FlightBooking WHERE BookingID="+bookingID+")
    The last WHERE condition will select only one single line from Flight (since FlightID is the primary key, I suppose).
    On that line, a single update is performed: the current value of AvailableSeats is incremented by the value returned by the subquery.
    This is standard SQL.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •