SQL Server Performance

Query help

Discussion in 'General DBA Questions' started by pinky, Oct 10, 2006.

  1. pinky New Member

    Hi friends,
    I want to delete some duplicate rows from table.how can delete rows.
    any one solve this problem.
  2. Roji. P. Thomas New Member

    quote:Originally posted by pinky

    Hi friends,
    I want to delete some duplicate rows from table.how can delete rows.
    any one solve this problem.

    Assuming you have a PK.

    DELETE FROM Your Table
    WHERE PK NOT IN(SELECT Min(PK) FROM [Your Table] GROUP BY [All Other Columns])


    Roji. P. Thomas
    http://toponewithties.blogspot.com
  3. pinky New Member

    what is that pk refers
  4. chiragkhabaria New Member

    Pk Means primary key, does your table has any primary key ??

    Chirag

    http://chirikworld.blogspot.com/
  5. chiragkhabaria New Member

  6. pinky New Member

    I used this query but it delets two duplicate values(101) in below table.
    DELETE FROM ST a
    WHERE EXISTS(SELECT b.SNO,b.SNAME,count(*)
    FROM ST b
    WHERE b.SNO=a.SNO
    AND b.SNAME=a.SNAME
    GROUP BY b.SNO,b.SNAME
    HAVING count(*) >1);

    I want

    SNO SNAME
    --- -------
    100 hgjhg
    102 ghgg
    101 gjhgh

    this type of result and below one is my table (st)

    SNO SNAME
    --- -------
    100 hgjhg
    102 ghgg
    101 gjhgh
    101 gjhgh
    any body plz solve this.
  7. thomas New Member

    quote:Originally posted by Roji. P. Thomas



    Assuming you have a PK.

    DELETE FROM Your Table
    WHERE PK NOT IN(SELECT Min(PK) FROM [Your Table] GROUP BY [All Other Columns])


    Roji. P. Thomas
    http://toponewithties.blogspot.com


    I'd have thought the presence of duplicates indicates the absence of a Primary Key?
  8. Madhivanan Moderator

  9. pinky New Member

    In this table
    dno dname dloc
    1 hr hyd
    2 admin hyd
    2 admin hyd
    3 finance hyd
    I want to delete only one row that is 2

    I want output like this

    dno dname dloc
    1 hr hyd
    2 admin hyd
    3 finance hyd

    I want sloution.any body solve this.


  10. patel_mayur New Member

    Step-1:

    Select dno, dname, dloc
    into #TmpTable
    From yourtable
    Group By dno, dname, dloc
    Having Count(1) > 1

    Step-2:

    Delete yourtable
    From #TmpTable
    Where yourtable.dno = #TmpTable.dno
    AND yourtable.dname = #TmpTable.dname
    AND yourtable.dloc = #TmpTable.dlock

    Step-3:

    Insert yourtable
    Select dno, dname, dlock
    From #TmpTable

    Drop Table #TmpTable


    Thx,
    Mayur.

  11. pinky New Member

    Thanks patel it's working but this is use full for removing lot of duplicaterows.


  12. Madhivanan Moderator

    Dont you look at the links posted?

    Madhivanan

    Failing to plan is Planning to fail
  13. pinky New Member

    Hi All,

    How to create temporary tables in oracle give some examples.
  14. Adriaan New Member

    Try an Oracle forum - this is Microsoft SQL Server territory.<br /><br />Good luck anyway.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  15. pinky New Member

    Hi all,
    I want to write MSDBA certification and how to prepare this certification exam any body having idea aboutb this plz tell me.
  16. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]I don't see this as a real problem and sounds like a classroom exercise.<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Dont you look at the links posted?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  17. Madhivanan Moderator

    quote:Originally posted by pinky

    Hi all,
    I want to write MSDBA certification and how to prepare this certification exam any body having idea aboutb this plz tell me.
    Post your question as a new topic

    Madhivanan

    Failing to plan is Planning to fail
  18. satya Moderator

    No why you need another thread, refer under SQL Certification forum section where you have enough information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  19. sonnysingh Member

  20. pinky New Member

    Hi all,

    I want to know the SQL DBA roles and activities.

    Thanks

    pinky
  21. FrankKalis Moderator

    Your question now isn't really related to the subject "Query help". Please start a new thread. But before search the forum. There's plenty on information already available on DBA duties.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  22. pinky New Member

    Hi all,
    I want know the sql server DBA roles and activities.
  23. Madhivanan Moderator

    quote:Originally posted by pinky

    Hi all,
    I want know the sql server DBA roles and activities.
    Didnt you read the replies posted?
    Do google search

    Madhivanan

    Failing to plan is Planning to fail

Share This Page