SQL Server Performance

read 2 tables - update a coloumn in one table

Discussion in 'Getting Started' started by surya42, Jul 17, 2011.

  1. surya42 New Member

    I want to write a stored procedure that reads 2 tables and sets ‘Status’ on Table 1 to ‘C’ if request is complete .
    Please advise – I am new to Sql programming . …..

    I have 2 tables

    Requests & Bookings (Table 1 and Table 2)
    For each request for a room there are many booking records .

    Read each record in Table 1 and corresponding records in Table 2.
    .
    If ToDateTime = system date or ToDateTime > systemdate on Table2 then Status needs to be changed to ‘C’ on Table 1 .

    Table 1

    RequestID 005
    RequestDate 1/1/2011
    Name Smith
    Email smth@abc.hotmail.com
    Tel 01552366662
    RoomNo 12
    Location T1
    Status NC etc etc
    --------------------------------------------------------------------------------------------------------

    Table 2

    BookingID 001
    RequestID 005
    RequestDate 1/1/2011
    FromDateTime 1/1/2011 09.00
    ToDateTime 1/1/2011 10.00
    BookedBy Smith

    BookingID 002
    RequestID 005
    RequestDate 11/1/2011
    FromDateTime 12/1/2011 09.00
    ToDateTime 12/1/2011 13.00
    BookedBy Smith

    BookingID 003
    RequestID 005
    RequestDate 1/1/2011
    FromDateTime 13/1/2011 09.00
    ToDateTime 13/1/2011 10.00
    BookedBy Smith

    BookingID 004
    RequestID 005
    RequestDate 14/1/2011
    FromDateTime 15/1/2011 09.00
    ToDateTime 15/1/2011 10.00
    BookedBy Smith

    If ToDateTime = system date or ToDateTime > systemdate then status needs to be changed to ‘C’ on Table 1 .
  2. satya Moderator

    Is this a classroom based Q?
  3. Madhivanan Moderator

    What have you tried so far?

    General logic

    update t1
    set t1.col=t2.col
    from table1 as t1 inner join table2 as t2 on t1.keycol=t2.keycol

Share This Page