I have created a temp table (@temptable) where i left a column blank because I will fill it with another select statement.
I have a table that has 5 rows called books:
BookNum | BookDesc | BookDate | ......
---------|----------|----------|--------
00 | A sto... | 6/6/2013 | ......
00 | {null} | {null} | ......
02 | The t... | 6/6/2013 | ......
00 | {null} | 6/6/2013 | ......
02 | {null} | 6/6/2013 | ......
and my temptable has a column that includes the BookNum:
...... | total_books | title | BookCode | CountOfBook
-------|-------------|-------|----------|-------------
...... | 4 | Once | 00 | {null}
...... | 4 | Once | 00 | {null}
...... | 4 | Once | 00 | {null}
...... | 4 | Once | 00 | {null}
...... | 4 | Once | 00 | {null}
...... | 4 | Once | 00 | {null}
...... | 4 | Four | 02 | {null}
...... | 4 | Four | 02 | {null}
And what I want to do is get the count of how many books I have of a specific book from the books table where the date is not null, and put it into the CountOfBook column for my @temptable, but I can't seem to figure it out. it should look like this:
...... | total_books | title | BookCode | CountOfBook
-------|-------------|-------|----------|-------------
...... | 4 | Once | 00 | 2
...... | 4 | Once | 00 | 2
...... | 4 | Once | 00 | 2
...... | 4 | Once | 00 | 2
...... | 4 | Once | 00 | 2
...... | 4 | Once | 00 | 2
...... | 4 | Four | 02 | 2
...... | 4 | Four | 02 | 2
as there are 2 of each book where the date is not null.