Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The following example works as well when your PK is just a subset of all table columns.</p> <p>(Note: I like the approach with inserting another surrogate id column more. But maybe this solution comes handy as well.)</p> <p>First find the duplicate rows: </p> <pre><code>SELECT col1, col2, count(*) FROM t1 GROUP BY col1, col2 HAVING count(*) &gt; 1 </code></pre> <p>If there are only few, you can delete them manually:</p> <pre><code>set rowcount 1 delete from t1 where col1=1 and col2=1 </code></pre> <p>The value of "rowcount" should be n-1 times the number of duplicates. In this example there are 2 dulpicates, therefore rowcount is 1. If you get several duplicate rows, you have to do this for every unique primary key.</p> <p>If you have many duplicates, then copy every key once into anoher table:</p> <pre><code>SELECT col1, col2, col3=count(*) INTO holdkey FROM t1 GROUP BY col1, col2 HAVING count(*) &gt; 1 </code></pre> <p>Then copy the keys, but eliminate the duplicates.</p> <pre><code>SELECT DISTINCT t1.* INTO holddups FROM t1, holdkey WHERE t1.col1 = holdkey.col1 AND t1.col2 = holdkey.col2 </code></pre> <p>In your keys you have now unique keys. Check if you don't get any result:</p> <pre><code>SELECT col1, col2, count(*) FROM holddups GROUP BY col1, col2 </code></pre> <p>Delete the duplicates from the original table:</p> <pre><code>DELETE t1 FROM t1, holdkey WHERE t1.col1 = holdkey.col1 AND t1.col2 = holdkey.col2 </code></pre> <p>Insert the original rows:</p> <pre><code>INSERT t1 SELECT * FROM holddups </code></pre> <p>btw and for completeness: In Oracle there is a hidden field you could use (rowid):</p> <pre><code>DELETE FROM our_table WHERE rowid not in (SELECT MIN(rowid) FROM our_table GROUP BY column1, column2, column3... ; </code></pre> <p>see: <a href="http://support.microsoft.com/kb/139444" rel="nofollow noreferrer">Microsoft Knowledge Site</a></p>
    singulars
    1. This table or related slice is empty.
    1. POHow can I delete duplicate rows in a table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMartin
    1. USMartin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou should have mentioned you got this from Microsoft's support site. http://support.microsoft.com/kb/139444
      singulars
      1. PO
      1. USTony_Henrich
    2. CO@Tony: That is correct. To my defense: I had this copied in my local programming wiki and wasn't even aware anymore where it came from.
      singulars
      1. PO
      1. USMartin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload