CODEWITHSUNDEEP
sqldatabaseoracle-database
user3808188
user3808188

Reputation: 577

Can I select from a destination table in a subquery?

Assume this is my Destination table:

    Name | Reason for Visit | Visit Number | Visit_date
    Dan  | Foo              | 1            | 04/03/2014
    Henry| Bar              | 1            | 05/07/2014
    Dan  | Tau              | 2            | 08/10/2014

I already have the Name, Reason_For_Visit and Visit_Date in my SOURCE table, and have to create the Visit_Number column. Am I allowed to sub-query the still-forming destination table like this and will I get the correct visit_number? 

    insert into DESTINATION
    NAME,
    REASON_FOR_VISIT,
    (select count(d.Name)+1 from destination d where d.Name=Name) AS VISIT_NUMBER,
    VISIT_DATE
    from SOURCE
    ORDER BY VISIT_DATE ASC;

If rows are inserted one by one, I feel like I should get the correct Visit_number.

Upvotes: 1

Views: 58

Answers (1)

SlimsGhost
SlimsGhost

Reputation: 2909

The answer to your question is YES - if you insert the first row, the second insert will see the first row already in the destination table.

Still, doing it row by row is an inefficient way to accomplish what you are doing. Why don't you try out something like this:

insert into destination
select 
    name, 
    reason_for_visit, 
    row_number() over (partition by name order by visit_date) as visit_number,
    visit_date
from source

The row_number() function will compute the visit_number correctly for you, and inserting all the rows with one query will definitely be more efficient.

Good luck!

Upvotes: 3

Related Questions