CODEWITHSUNDEEP
sqltemp-tables
rjbogz
rjbogz

Reputation: 870

updating a temp table with data from two tables

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.

Upvotes: 0

Views: 2318

Answers (1)

sourcecode
sourcecode

Reputation: 1802

Try this , You can use subquery in update statement - which will help you-

   update tempTable t
    set CountOfBook = (select count(*) as CountOfBook_2 
                        from Books 
                       where BookData is not null
                       Group by BookCode 
                       having BookCode=t.BookCode );

sqlfiddle example -> using oracle 11 g => fiddle

Update: try using this: link referred

   update tempTable 
    set CountOfBook = (select count(*) as CountOfBook_2 
                        from Books 
                       where BookData is not null
                       Group by BookCode 
                       having BookCode=t.BookCode)
    FROM tempTable as t;

Upvotes: 2

Related Questions