Update one table based on count from another

[SatishPutcha]

Hello All

I heve two tables Publishers and Books.

Publishers has:
PublisherID
PublisherName
BookCount

Books has:
BookID
PublisherID
BookName.

I want a query that will update the BookCount field for every Publisher taking the number of books each one of them has in the Books table.

What would be the mySQL Update query for this?

Hope I have given enough information. I will gladly provide more.

Regards
Satish

Thanks and Regards
Satish Kumar

 

[cLFlaVA]

what version of mysql are you running?



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SatishPutcha]

Sorry for the delayed response. The mySQL version is 4.1.14

 

[guelphdad]

Book count should not be stored in your table, it is a redundant field and can be calculated on the fly when you need to display it.

Since this is a very basic question, looking suspiciously like a homework question, perhaps you could show us what you have attempted so far to get an answer. We can assist you with your query from there.

 

[SatishPutcha]

Yes, I understand this is a basic question but it is NOT for a homework and part of a larger project so please bear with me.

The main issue is....
On the page the user can select the type of book "Fiction", "Autobiography" or "Travel". We have to show all publishers with atleast one book of that kind and also show the other books those publishers have. When the user wants to sort it has to do so by the total number of books for each publisher but our current query is...

Select Publishers.PublisherID, Count(BookID) AS TotalCount FROM Books INNER JOIN Publishers.PublisherID ON Publishers.PublisherID = Books.PublisherID WHERE BookTypeID = 1 GROUP By PublisherID ORDER BY TotalCount DESC

...and will give the count of number of books of the selected type the Publisher has.

So if the Admin Control Panel can have an update query which will update the total count it will make it easier and I can do something like this

Select Publishers.PublisherID, BookCount FROM Books INNER JOIN Publishers.PublisherID ON Publishers.PublisherID = Books.PublisherID WHERE BookTypeID = 1 GROUP By PublisherID ORDER BY BookCount DESC

Any ideas are welcome.

~Satish