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

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,

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) Received on Fri Feb 18 2005 - 16:15:04 CST