Complex MySQL Query

Discussion in 'MySQL' started by FishSword, Mar 5, 2011.

0
  1. #1
    Hiya,

    I have a table that contains book purchase information for an online book shop.
    The database structure can be found below:


    • Purchase_ID:
      Used as a unique identifier for the purchase

    • Book_ID:
      Used to store the book that was purchased

    • Customer_ID:
      Used to store the book purchaser

    • Purchase_Date:
      Used to store the purchase date.

    • Book_Selected:
      Define whether or not book is entered into competition.

    Upon clicking a link containing a particular "Purchase ID", how do I change the value of the "Book_Selected" field to "1", whilst changing the "Book_Selected" field values to "2" for any other Purchase ID's that have the same "Book ID".

    Cheers ;)
     
    FishSword, Mar 5, 2011 IP
  2. jkl6

    jkl6 Peon

    Messages:
    70
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Not sure why you want to do this, but how's this:
    update table set book_selected=1 where purchase_id=x;
    update table set book_selected=2 where purchase_id<>x;
     
    jkl6, Mar 5, 2011 IP
  3. FishSword

    FishSword Active Member

    Messages:
    131
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #3
    I want to provide functionality to select a book and user/purchaser for a book competition. The book and user/purchaser that has "Book_Selected" value of "1" will be selected for the competition, and a "Book_Selected" value of "2" will not be selected.
    If any books have a "Book_Selected" value of "0", it shows that the book has not yet be selected in the competition, therefore an option will be provide to choose a book and a user/purchaser.

    Is it possible to change the field to either of the 3 values in one query?

    I hope this makes sense.
     
    FishSword, Mar 5, 2011 IP
  4. jkl6

    jkl6 Peon

    Messages:
    70
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I don't think it's possible to do it in 1 query, but you can group them in 1 transaction like this:
    START TRANSACTION;
    UPDATE ....
    UPDATE ....
    COMMIT;
    Take a look at the mysql documentation on transactions.
     
    jkl6, Mar 5, 2011 IP
  5. benims

    benims Peon

    Messages:
    43
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    hmm diffucult question
     
    benims, Mar 8, 2011 IP
  6. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #6
    JKL6's suggestions are nearly correct, though they missed the second condition of ensuring the bookID was the same for all the items to be set to a 2.

    Why are you so concerned to do it in one query?
     
    AstarothSolutions, Mar 10, 2011 IP
  7. bytes

    bytes Peon

    Messages:
    39
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #7
    if I understood you right, you can do this with single query, without wrapping in transaction (which I have to say won't work if you use non transactional storage engine like MyISAM for example) :

    
update books
left join `books` books2 on books2.book_id = books.book_id and books2.purchase_id != books.purchase_id
set `books`.`book_selected` = 1, books2.`book_selected` = 2
where `books`.`purchase_id` = 1;
    Code (markup):
     
    bytes, Mar 12, 2011 IP