Complex MySQL Query

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

 

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;

 

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.

 

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.

 

hmm diffucult question

 

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?

 

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):