Professional Web Applications Themes

Need some Strawberry! - MySQL

OK my turn to ask for help again. I have a table of rooms and beds. There are varying numbers of beds in a room. I want to assign the beds in room and bed order so first room 1 bed 1 then room 1 bed 2 then room 1 bed 3 then room 2 bed 1 and so on. So I'm looking for a query that will update the "next" available bed with the id of the person who is booking it. available beds have a NULL in booked and booked ones are shown in this test table with ...

  1. #1

    Default Need some Strawberry!

    OK my turn to ask for help again.

    I have a table of rooms and beds. There are varying numbers of beds in a
    room. I want to assign the beds in room and bed order
    so first room 1 bed 1
    then room 1 bed 2
    then room 1 bed 3
    then room 2 bed 1
    and so on.

    So I'm looking for a query that will update the "next" available bed with
    the id of the person who is booking it.
    available beds have a NULL in booked and booked ones are shown in this test
    table with a letter.

    the query
    SELECT `a`.*
    FROM `beds` `a`
    LEFT JOIN `beds` `b` ON `a`.`id` - 1 = `b`.`id`
    WHERE `a`.`booked` IS NULL AND `b`.`booked` IS NOT NULL
    will pull out the row that I am interested in, but turning it into an update
    query doesn't work because, upon updating one row the next row becomes a
    candidate for update and so on.
    I want to do something like:

    UPDATE`beds` `a`
    LEFT JOIN `beds` `b` ON `a`.`id` - 1 = `b`.`id`
    SET `a`.`booked` = {person_id}
    WHERE `a`.`booked` IS NULL AND `b`.`booked` IS NOT NULL
    LIMIT 1

    but of course LIMIT cannot be used in a multi table update.

    Anyone got any ideas?


    Paul Guest

  2. #2

    Default Re: Need some Strawberry!

    On May 2, 9:53 pm, "Paul Lautman" <com> wrote: 


    Well, seeing as it does have my name stamped all over it:

    UPDATE beds b1 JOIN (
    SELECT min( id ) id
    FROM beds
    WHERE booked IS NULL
    )b2 ON b1.id = b2.id
    SET b1.booked = 43

    strawberry Guest

  3. #3

    Default Re: Need some Strawberry!

    On 2 May, 23:25, strawberry <com> wrote: 






    >
    > Well, seeing as it does have my name stamped all over it:
    >
    > UPDATE beds b1 JOIN (
    > SELECT min( id ) id
    > FROM beds
    > WHERE booked IS NULL
    > )b2 ON b1.id = b2.id
    > SET b1.booked = 43- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Actually I think I was trying to make it too complicated. I should
    (hopefully) be able to do something like:
    UPDATE `beds`
    SET `booked` = {person_id}
    WHERE `booked` IS NULL
    ORDER BY `id`
    LIMIT 1

    Captain Guest

Similar Threads

  1. Strawberry perl: use Win32::Process ; ?
    By Bernie in forum PERL Modules
    Replies: 2
    Last Post: November 19th, 12:24 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139