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

but updating the seats seems harder as you are only given BookingID

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

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,

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

it's DB2, ta

Here's what I would do: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.