updating columns by select

[panza]

I've got 2 tables, table A lists all categories and the second table lists all books (name, category). Now, I'm using the following statement to view the number of books per category:<br>
<br>
SELECT category, COUNT(category) FROM books GROUP BY category<br>
<br>
Although this ignores all categories without any books, it works great and is very fast. <br>
But I can't seem to update a column 'entries' in the categories table to the number of books per category. How do I do that? One statement? Thanks a lot in advance!<br>
<br>

 

[calahans]

Hi,<br>
<br>
If you are using SQL Server you can use:<br>
<br>
UPDATE CATEGORIES<br>
SET ENTRIES=A.NUMB<br>
FROM (SELECT category, COUNT(category) AS NUMB FROM books GROUP BY category) A<br>
WHERE CATEGORIES.CATEGORY=A.CATEGORY<br>
<br>
If you are using Ingres the format is:<br>
<br>
UPDATE TABLEA<br>
FROM TABLEB<br>
WHERE...<br>
<br>
HTH<br>
<br>
Cal

 

[panza]

It doesn't work well, I'm using MySQL and I tried something easier and similiar to yours, but not even that works:<br>
<br>
SELECT * FROM books WHERE category IN (SELECT * FROM categories)<br>
<br>
Wtf? Any idea?<br>
<br>
Thanks!<br>

 

[bigscouse]

Instead of <br>
SELECT * FROM books WHERE category IN (SELECT * FROM categories)<br>
Try <br>
SELECT * FROM books WHERE category IN (SELECT category FROM categories)<br>
<br>
<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>

 

[stoleru]

In Oracle 7:<br>
UPDATE categories a<br>
&nbsp;&nbsp;&nbsp;&nbsp;SET entries=(SELECT nvl(b.counts,0)<br>
&nbsp;&nbsp;&nbsp;&nbsp;FROM (SELECT c.category,COUNT(c.category) counts<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM books c GROUP by c.category) b,categories d<br>
&nbsp;&nbsp;&nbsp;&nbsp;WHERE d.category=b.category(+) AND d.category=a.category);<br>
<p>Eduard Stoleru<br><a href=mailto:aeg@ziua.ro>aeg@ziua.ro</a><br><a href= > </a><br>