Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    25

    Unanswered: Another Update question

    Two table (that matter right now): rooms and events.

    Here's the failed code:

    UPDATE rooms SET available = 'No', availableQty = 0 WHERE rooms.event_ID = events.event_ID AND events.active = 'No'

    Not quite sure what to do.

    Thanks,

    mvreade

  2. #2
    Join Date
    May 2007
    Posts
    8
    Hi,

    Actually if you see your query properly it does not mention both the tables. But you are using those in WHERE clause.

    Try this...

    UPDATE rooms,events SET rooms.available = 'No', rooms.availableQty = 0 WHERE rooms.event_ID = events.event_ID AND events.active = 'No'

    Thanks,

  3. #3
    Join Date
    Sep 2006
    Posts
    25
    Thanks, Swapnil (again coming to the rescue)...

    Comparing this Update with the one you posted two days ago (http://www.dbforums.com/showthread.php?t=1622982), I'm not sure why the other one required a subquery and this one doesn't...

    If you could please clarify that for me I'd be most thankful.

    All the best,

    mvreade

  4. #4
    Join Date
    May 2007
    Posts
    8
    In my earlier reply for thread 1622982,

    We need to calculate SUM (For this we have used inner query) and then update respective table.

    However,

    In current post we just need to update respective table checking related ID's in two different table.

    So Inner query was used specifically for calculating SUM.

    Thanks,

  5. #5
    Join Date
    Sep 2006
    Posts
    25
    Thanks for the clarification, Swapnil.

    All the best,

    Michael Reade

Posting Permissions

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