SQL Error: ORA-00001: unique constraint (CHRIS.FILM_U) violated With this is in place, any attempts to add copies will throw an error: insert into films values ( Include in this all the columns that form the duplicate group you identified at the start: alter table films add constraint The answer's easy: create a unique constraint. Either because the code doesn't handle concurrency properly (note: this is an easy mistake to make) or someone ran a script directly on the database.Įither way, you're still left dealing with duplicates. You could do this by adding some front-end validation. There will still be times between job runs where the table could have duplicates.Ī better solution is to stop people entering new copies. So you create a job to remove the duplicates. "Please remove duplicate customer accounts".Īll that hard work for nothing! OK, you've got your delete ready, so you can run it again.Īfter a while you'll likely become bored of this. You've gone to all the effort of removing the duplicates. Luckily Oracle Database has many tricks to help you delete rows faster. To do this, replace min() with min(rowid) in the uncorrelated delete: delete filmsĪnd hey presto, you've removed all the extra rows! If there are many duplicates, this can take a long time. So you can use this value to identify and remove copies. That is, it states where on disk Oracle stores the row. The rowid.Īll rows in Oracle have a rowid. If the rows are fully duplicated (all values in all columns can have copies) there are no columns to use! But to keep one you still need a unique identifier for each row in each group.įortunately, Oracle already has something you can use. Do this by deleting those not in this subquery: delete filmsīoth these methods only work if you have a column which is not part of the group. So you want to get rid of all the rows not in this set. This gives you the value of the first entry for each combination. Then add the id or other unique column as appropriate. The subquery here is like the original group by example you made to find the copies. Uncorrelated DeleteĪn uncorrelated delete is similar to the previous example. But the column can have copies in other groups. Remember: this solution will only work if the column you're taking the minimum of is unique for each group. There is always one oldest row in each batch. This finds, then deletes all the rows that are not the oldest in their group. Take the minimum value for your insert date: delete films fĪnd f.uk_release_date = s.uk_release_date You need to do this on your duplicate column group. Correlated DeleteĬorrelated means you're joining the table you're deleting from in a subquery. A good example of this is an id column which is the table's primary key. In contrast, if the defining column is unique across the whole table, you can use an uncorrelated delete. If your driving column is unique for each group, but may have duplicates elsewhere in the table, you'll need a correlated delete. insert date, id, etc.) that is not one of the copied values.Īssuming you have this, there are two ways you can build your delete. To do this you'll need another column in the table (e.g. For example, you might want to preserve the oldest row. To do this, you must first decide which rows you want to keep. It's better to build a single statement which removes all the unwanted copies in one go. But this is unworkable if there are a large number of duplicates. If there is only a handful, you could do this by hand. For simplicity, I'm going to assume that either the rows are exact copies or you don't care which you remove. Now you've identified the copies, you often want to delete the extra rows. How to Delete the Duplicate Rows Delete key1 by Büşra ÖZCOŞKUN CC BY-SA 4.0 With a list of the duplicates in hand, it's time for the next step! Select f.*, count(*) over (partition by title, uk_release_date) ct Or you could use the with clause: with film_counts as ( So if you have more than one email address in your user accounts table, how do you know who is who? USER_ID EMAIL_ADDRESS FIRST_NAME LAST_NAME It is common for websites to use your email address as your username. Or maybe there are only one or two column values that are copies. But all the "real world" information is repeated: FILM_ID TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES RATING This means the rows are no longer exact copies. For example, it's common for tables to have an ID column. For example, the two rows below hold identical information: TITLE UK_RELEASE_DATE LENGTH_IN_MINUTES BBFC_RATINGīut often you need to find copies on a subset of the columns. So you're looking for two (or more) rows that are copies of each other.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |