How to prevent duplicate rows in insert query using Oracle?

I have table A in Oracle that has a primary key (id). I need to insert data into this table.

How do I prevent duplicate rows?

--------------Solutions-------------

If the id column is marked as the PK you will not be able to insert a duplicate key, the server will throw an exception.

If you will insert duplicate data with different key - that's a logic that you need to deal with (like putting a unique constraint on the actual data) or do a check before you insert.

If you mean that you have rows that are identical (apart from the primary key) and you want to know how to delete them then do:

select col2, col3, ...coln, min(id) from A
group by col2, col3, ...coln

(I.e. select on all columns except the id.)

To get the unique instances do

delete from A where id not in
(select min(id) from A
group by col2, col3, ...coln) as x

to delete all rows except the unique instances (i.e. the duplicates).

First you would create a unique id on this table that has a sequence. Then when you insert into that table, in the insert statement you can use:

tableName_sn.nextval

in a statement like:

inserti into tableName (id) values (tableName_sn.nextval)

to get the next unique key in the sequence. Then if you use this key you will guarantee that it is unique. However, the caveat to that is if someone just entered a key not using the nextval function you will get primary key violations.

Category:sql Time:2009-10-31 Views:1
Tags: sql oracle

Related post

  • How to prevent duplicate records being inserted with SqlBulkCopy when there is no primary key 2010-04-07

    I receive a daily XML file that contains thousands of records, each being a business transaction that I need to store in an internal database for use in reporting and billing. I was under the impression that each day's file contained only unique reco

  • In MySQL, how do I write a query to skip a duplicate row while inserting, when there's no unique field (primary key)? 2010-07-29

    I am trying to insert rows into a table that has no unique field or primary key. How can I write a query that will simply ignore the insert if there already exists a row with the exact same values on all fields -- a duplicate row? Thanks. -----------

  • How can i prevent duplicate rows being selected in a select query? 2011-05-19

    I have been given the task of selecting key data from an Oracle database, but I am noticing that my select is returning duplicate rows. I don't need them for my report yet I don't want them to delete them. Could someone help to get only the data that

  • Prevent duplicate rows with all non-unique columns (only with MySQL)? 2010-06-02

    How do I prevent a duplicate row from being created in a table with two columns, neither of which are unique? And can this be done using MySQL only, or does it require checks with my PHP script? Here is the CREATE query for the table in question (two

  • Prevent duplicate rows in MySQL without unique index/constraint? 2011-10-11

    I'm writing an application that needs to deal with millions of URLs. It also needs to do retrieval by URL. My table currently looks like this: CREATE TABLE Pages ( id bigint(20) unsigned NOT NULL, url varchar(4096) COLLATE utf8_unicode_ci NOT NULL, u

  • How to prevent duplicate values from inserting mySQL considering two columns? 2011-10-20

    Here is my table: Table Name: UserLinks Link_ID User_1 User_2 1 234325 100982 2 116727 299011 3 399082 197983 4 664323 272351 Basically, in this table a duplicate value is: Link_ID User_1 User_2 1 232 109 2 109 232 I have looked around and found that

  • Removing duplicate rows using delete query? 2011-05-24

    This question was asked for an interview, I still cant find an answer by googling it. I have a table with two columns. Table doesn't have any primary key. I want to delete the duplicate rows. I can do it by alter table but they want a delete query. H

  • Preventing duplicate rows when using UUIDs for primary key 2011-07-22

    I've got a database table in DB2 (v9.5) like the following: CREATE TABLE license(key CHAR(16) FOR BIT DATA NOT NULL PRIMARY KEY, name VARCHAR(32) not null, startDate TIMESTAMP not null, data XML); (I'm using UUIDs for the primary key as shown in this

  • Preventing duplicate rows based on a column (MySQL)? 2011-11-06

    I'm building a system that updates its local database from other APIs frequently. I have Python-scripts set as cron jobs, and they do the job almost fine. However, the one flaw is, that the scripts take ages to perform. When they are ran for the firs

  • Strategy for vast number of potential duplicate rows upon INSERT in MySQL 2012-04-12

    I have approximately 50,000 3rd party data feeds that will be read every five minutes. The idea is to check for updated content. I have the following tables: feeds id name url posts id feed_id title content url unique_hash (based on url + title) My f

  • Adding duplicate row when insert else update 2012-04-28

    I was using the code below, when user_id wasnt set as unique and it entered the same data twice with the same user_id. So I have set it to unique and now just get Error, query failed. All help is greatly appreciated :( if (empty($err)) { $thesis_Name

  • Preventing certain rows from being deleted in Oracle 2012-01-03

    I want to prevent any row with VERSIONID=1 from being deleted in a certain table. I also want to log this in an audit table so we can see when this happens for logging purposes. I'm trying to do this with a trigger: CREATE TRIGGER TPMDBO.PreventVersi

  • using ON DUPLICATE KEY UPDATE insert query for update already exist row 2011-05-28

    connection() { OleDbConnection nwindConn=new OleDbConnection(); MySql.Data.MySqlClient.MySqlConnection con= new MySqlConnection(); MySqlCommand cmd; con.ConnectionString ="server=localhost;" + "uid=root;"+ "pwd=;" + "database=globasys;" ; DateTime dt

  • How to duplicate row in SQL Query result 2010-10-20

    SELECT * FROM TABLE1 ID Name 1 SOF 2 USER I want query which can one or two or four .... times ID = 1 in row like What will be the Query of this in Ms.Access? ID Name 1 SOF 1 SOF 1 SOF 2 USER --------------Solutions------------- Following example use

  • Preventing duplicates in an SQL query? 2010-07-03

    Is there an easy way when inserting a new record to make it fail if one of the fields is a duplicate of one of the other fields? I don´t want the field to be a primary key or anything like that... --------------Solutions------------- Set the column a

  • Regarding multiple insertion of rows through single query.(oracle) 2011-02-24

    Is the mechanism used in multiple insertion of rows in the table through single query is same as that of inserting single row with a single query.If not what is the exact mechanism? --------------Solutions------------- If you are fetching the data fr

  • Duplicate Rows From HQL Query When Returning Bag Property 2011-02-24

    in my query I am using multiple tables, some of which cannot be set up as normal nhibernate relationships, so I am forced to return rows made up of individual columns rather than instances of entities. One of the properties I want to return is a bag/

  • Exclude duplicate rows in mysql query results 2011-02-08

    I want to count the number of records in database from more than two tables that are joined. For example I have a table like this. table jobd + name 1 | jobA 2 | jobB tableA imgeid + orderid + jobid 1 | 1 | 1 2 | 2 | 1 3 | 3 | 1 4 | 4 | 1 (this order

  • insert query into oracle database 2012-01-21

    Possible Duplicate: How to handle a single quote in Oracle SQL My Senario: I want to save the query(not result set) in the database. I am using Java as the front end; My Table(Querytab) has the following Fields: sno VARCHAR2(1024) QUERY VARCHAR2(4000

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.363 (s). 13 q(s)