Re: How can I insert and avoid failing on account of duplicate rows?

<fizbin_at_gmail.com> wrote in message
news:1108764903.968231.110450_at_l41g2000cwc.googlegroups.com...
> This is more a general SQL question than an oracle-specific question,
> but if there's a nice Oracle-specific answer, I'll take it.
>
> I have a table, call it:
>
> CREATE TABLE ROOMS_TO_CLEAN (
> BUILDING_ID NUMBER NOT NULL,
> FLOOR_ID NUMBER NOT NULL,
> ROOM_ID NUMBER NOT NULL,
> PRIMARY KEY (BUILDING_ID, FLOOR_ID, ROOM_ID)
> );
>
> We'll call a 3-tuple in this table a "dirty" room.
>
> Now, because this is a made up example, trust me that it is completely
> unfeasible to have a single master list of all potential rooms.
>
> I want to insert a bunch of rows into this table, say with a statement
> like this:
> -- Valentine's Day parties were uniformly messy
> INSERT ROOMS_TO_CLEAN(BUILDING_ID, FLOOR_ID, ROOM_ID)
> SELECT m.BUILDING_ID, m.FLOOR_ID, m.ROOM_ID
> FROM PartyLocations m
> WHERE m.PartyDate = '2005-02-14'
>
> However, if some of these rooms are already marked as dirty (i.e. exist
> as rows in ROOMS_TO_CLEAN), this entire insert will fail. Now, I don't
> care that these rooms were already known to be dirty, I just want to
> mark them dirty.
>
> So here's my question:
>
> Is there any simple way to say "insert but just silently skip over rows
> that were already in the table"?
>
> Modifying my select statement so as to join against ROOMS_TO_CLEAN and
> explicitly excluding those rooms already in the table does not to my
> mind count as "simple". (since in the original problem that prompted
> this, the select in question is already joining three tables to begin
> with and is getting quite hairy)
>

try putting a minus in your subquery -- but if this gets beyond theoretical, then watch out for performance issues

also, look at the MERGE command (10g, maybe 9i?) -- may not qualify as simple, but is designed for this type of scenario

++ mcs Received on Fri Feb 18 2005 - 16:34:43 CST