MySQL Help - writing update statement

[scottmso]

Hey all:

I'm trying to write an SQL update statement for a project. There are three tables that I am working with here: "Books", "Transactions", and "Includes". "Includes" connects the other two tables by the "Order_ID" and "Book_ID" keys.

What I am trying to do here is set the "Total_Amount" column for each "Transaction" to be equal to the sum of the "Price" of all books for which there exists an entry in the "Includes" table that references the Order_ID (of the Transaction) and Book_ID (of the Book).

I'm getting Error 1242/"Subquery returns more than 1 row". I would suppose that the solution is rather simple but cannot get myself to think of it off the top of my head. Any advice would be greatly appreciated..thanks

Here is my code:

update Transactions
	set Total_Amount = 
		(select sum(Price) from Books 
			where ((select Book_ID from Includes) = Book_ID AND
			(select Order_ID from Includes) = Order_ID));

 

[mikeblas]

Your query will set all rows in the Transactions table to the same value. The error your getting is because of the predicate you have on the fourth and fifth lines, where you compare a single value with the result of a statement that will return a list of values.

 

[JohnleMVP]

You may consider joining the Books and Includes tables, it should simplify your sub query.