Printable View
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?
why not insert into a temporary table then switch to the real one, instead of inserting into the one you're selecting from?
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.
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.
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.