insert as select on mutating table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: insert as select on mutating table

  1. #1
    Join Date
    Aug 2000
    Posts
    132
    Hi All,

    We are trying to populate one table as an insert as a select from several other tables including the table that we are inserting into. It works when there are already records in the table but not on the initial insert (b/c on the conditional operator on the inserting table). Any suggestions?


  2. #2
    Join Date
    Jun 2000
    Posts
    417
    why not insert into a temporary table then switch to the real one, instead of inserting into the one you're selecting from?

  3. #3
    Join Date
    Aug 2000
    Posts
    132
    i thought about that but i'm struggling with the select as. this is for a web site we're we want to show users their "tracked" items, but, when they have the option to delete their tracked items (where delete really means don't show). So i'm struggling with the initial insert vs. subsequent ones.

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    oh so it's not just one batch thing. in that case using a temporary table every time isn't really an option :)

    can you give an example of your table structure and what you're trying to do?

    a basic layout i would have based on what you said is

    user profile table
    ------------------
    user_id
    first name
    last name
    whatever else you want

    some sort of inventory table(s)
    ------------------------
    item id
    name
    description
    etc

    user tracked items table
    ------------------------
    user_id
    item_id
    other important information (date tracked, whatever)

    when a user decides to track an item, insert row into the user tracked items table with the user_id and item_id. when they decide not to track it anymore, delete the row.

    the query just needs to join the user and inventory tables with the lookup in user tracked items table.

    if you still want the record that the item was tracked, add another column to the tracked table which is a flag to either show or not show the item, and keep the row around instead of deleting.

  5. #5
    Join Date
    Aug 2000
    Posts
    132
    My java developer decided to do it as separate methods, but, here's what we were attempting to do. We have 4 tables that were pulling the rows for the tracker table, plus the need to let users flag ones as not visible in their tracker. I had the insert as select working when there were items in the table, but on the initial population it would work b/c there were no matches on the join. I think temp table (plus maybe a trigger re: the don't show flag) was the way to go b/c it would eliminated dups. Anyway thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width