I have a table duplicate which shows datas as:  select * from ot.duplicate;     I wanted to delete the duplicate data from the table where I tried:  delete  from (select * from(select * from ot.duplicate a union select * from ot.duplicate b) t1);   But I am getting error:  ORA-01752: cannot delete from view without exactly one key-preserved table
15193
I'm testing something in Oracle and populated a table with some sample data, but in the process I accidentally loaded duplicate records, so now I can't create a primary key using some of the columns.  How can I delete all duplicate rows and leave only one of them?
20783
I have a table that looks like this:  +----------------------------------------+ |Name                | kode      | jum   | +----------------------------------------+ | aman               |kode1      | 2     | | aman               |kode2      | 1     | | jhon               |kode1      | 4     | | amir               |kode2      | 4     | +--------------------+-----------+-------+   How can I make a view like this with MySQL?                      kode1    kode2     count aman                  2         1        3   jhon                  0         4        4 amir                  0         4        4
18921
Is it possible to perform a Delete from a Data Extension using Query or something?  Like we have Insert/Update option in a query, I would like to delete.   I know this can done using API but is that the only way to perform automatic deletions from a data extension?
70647
Is a   select *  from myView   faster than the query itself to create the view (in order to have the same resultSet):  select * from ([query to create same resultSet as myView])   ?  It's not totally clear to me if the view uses some sort of caching making it faster compared to a simple query.
93615
I am trying to delete all the duplicates but keeping single record only (shorter id). Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones. DELETE FROM emailTable WHERE id IN (  SELECT * FROM (     SELECT id FROM emailTable GROUP BY email HAVING ( COUNT(email) &gt; 1 )  ) AS q )  Its MySQL. DDL CREATE TABLE `emailTable` (  `id` mediumint(9) NOT NULL auto_increment,  `email` varchar(200) NOT NULL default '',  PRIMARY KEY  (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=298872 DEFAULT CHARSET=latin1
71558
I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this...  A great answer was given to building a composite key from multiple foreign keys on  but I want to do this from multiple tables. I am using Oracle 11g.  foreign key (Name, BoughtFrom, TimeBought)    references the_other_table_name (Name, BoughtFrom, TimeBought)   Imagine that the "references" line actually includes multiple tables. I'm not even sure that the above syntax will work with Oracle.  I have one table "Student" and one table "Parent". It is a many-to-many relationship. My association table is named "Relation" and I would like to make a composite primary key from "PAR_ID" and "STU_ID". Both are foreign keys.
18538
While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either:  ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or INSERT IGNORE implies an invitation for other kinds of failure to slip in unannounced.  Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?
79002
I have all kinds of views in mysql with employee data.  I have tried to use methods like this - but they simply don't work for views.    I end up getting more of a LEFT JOIN as a result.  Is there a way to merge two views without including duplicates in mysql - I am currently using a 4 view process to duplicate this.
12448
I have a custom object with name and a lookup to contact among other fields.  The need that i have is that i need to make sure that only record name should not be duplicated for a contact.  There can be duplicate names as long as its not the same contact. We should not allow duplicate names for the same contact.  Can we achieve this using validation rules? I am trying to avoid using triggers if this can be achieved using validation rules.
80569
We are migrating our database to a new schema but want to validate the data has been moved correctly.   Traditional data comparison tools can compare two databases for differences if the schemas are the same. In our case there have been changes to the table designs but all the data from the old schema is in the new one, it's just been moved around a bit, and I need to make sure it is correct. We have tens of millions of rows so manual inspection is not an option.  Are there any tools which could aid in this type of comparison?  If not are there any libraries/frameworks which could help kick start the development of a custom solution?  I'm happy to use a database specific solution if necessary, in this case for SQL Server 2008.  My soluton: I'm comparing the two data sets by creating a VIEW of each table on the old database with the same fields as the new database table.   I then compare the data using the technique described here:   I'm lucky in this migration as the overall table structure is similar to the old database, with fields moved from one table to another, dropped or added. In the case of the dropped and added there is nothing to compare, for the fields which have moved or been aggregated I do calculations in the view to provide the correct information for comparison.  The UNION comparison shows me only the rows with differences, so as soon as the data is correct I get an empty result set.
79420
If I have a table  CREATE TABLE users (   id int(10) unsigned NOT NULL auto_increment,   name varchar(255) NOT NULL,   profession varchar(255) NOT NULL,   employer varchar(255) NOT NULL,   PRIMARY KEY  (id) )   and I want to get all unique values of profession field, what would be faster (or recommended):  SELECT DISTINCT u.profession FROM users u   or  SELECT u.profession FROM users u GROUP BY u.profession   ?
96882
Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:  select * into dbo.table2 from dbo.table1;   This is just a simple copy of the table; all of the indexes / constraints are missing. How can I copy a table structure (without using a backup)?  I am primarily looking to do this manually, but if that's not possible I'll accept any solution.
115476
I have a database that containes user data and need to dig up only the entries where two attributes are duplicate between users (surname and social security number). I need all of the attributes for these entries, such as email and firstname.   I first used group by to get every user where those two values are duplicates with this query:  SELECT *  FROM jasenet  GROUP BY hetu, sukunimi  HAVING count(*) &gt;= 2  ORDER BY sukunimi;   This returns all duplicate entries. After some research I tried to use IN operator in order to find out each individual duplicate person with this query:  SELECT *  FROM jasenet  WHERE sukunimi  IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) ORDER BY sukunimi;   This works somewhat because I get each individual with duplicate surname, but in addition to this I need to be able to have the query use the social security number as well (hetu).  I tried this query, but it didn't work:  SELECT *  FROM jasenet  WHERE sukunimi  IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) AND hetu IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) ORDER BY sukunimi;   How can I use two columns returned from a query in another query?
69888
I have a custom object, with a Text Name (ID) field. When I upsert two records with identical Name/ID, but otherwise different values, in two separate upsert statement, one record results, as expected.  When I create a list, add these records to the list, and then upsert the list, I get a DUPLICATE_VALUE error. What gives? By definition, upsert should not do this, should it? Even worse: it doesn't matter whether a record with the identical Name already exists or not, the upsert will fail in both scenarios.  By the way: by default upsert a does not use the Name/ID field as identifying/unique field, as you might expect. You need to do upsert a Name  Complete code:  List&lt;Flight__c&gt; flights = new List&lt;Flight__c&gt;(); Flight__c f1 = new Flight__c(); f1.Name = 'ABC123'; f1.Flight_Number__c = 'ABC'; flights.add(f1);  Flight__c f2 = new Flight__c(); f2.Name = 'ABC123'; f2.Flight_Number__c = '123'; flights.add(f2);  upsert flights Name;      Error message: Line: 12, Column: 1 System.DmlException: Upsert failed.   First exception on row 0; first error: DUPLICATE_VALUE, Duplicate   external id specified: ABC123: [Name]   Name being the standard identifying field of a custom object. You cannot edit the properties of this field, e.g. making it unique or not unique.
47138
I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be rejected. How can I achieve that?   My table definition:  CREATE TABLE mytable (     id serial primary key,     col1 int NOT NULL,     col2 int NOT NULL,     unique (col1, col2) )
59470
I am trying to remove the duplicates items from a big Excel sheet. I would like to remove all rows that have a duplicate code values (sample below)  Before:      Name     Code item_01  xxxx01 item_02  xxxx02 item_03  xxxx01 item_01  xxxx03 item_04  xxxx04   After:  Name     Code item_01  xxxx01 item_02  xxxx02 item_01  xxxx03 item_04  xxxx04   I have played with the "Remove Duplicates" features, but I can't get the result that I want.   Any help? Thanks
47794
I know the statement:  create table xyz_new as select * from xyz;   Which copies the structure and the data, but what if I just want the structure?