how to delete duplicate col.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2012 at 12:25 pm

  #1514443

  SQLRNNR (7/18/2012)

  ---

  Lynn Pettis (7/18/2012)

  ---

  SQLRNNR (7/18/2012)

  ---

  lokeshvij (7/18/2012)

  ---

  Jason: I always wonder when I use CTE to delete duplicate rows.

  CTE being ANSI compliant, why does it let us delete data from CTE? I am sure ANSI norms does not allow that. Has Microsoft gone a step ahead? 🙂

  Not sure it is just Microsoft. I think Oracle allows the same.

  Not sure, never tried it with their subquery refactoring clause.

  Looks like most examples i find use a subquery or a ranking function (very similar to the cte).

  While at Honeywell I just referred to the Oracle subquery refactoring clause as a CTE. Same thing, just Oracle handles them better at the moment. Oracle can treat it as an in-line query or a temporary table depending on what is needed for performance.

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  July 18, 2012 at 12:28 pm

  #1514446

  I do agree with Lynn on performance perspective.

  - Lokesh

  ~ Lokesh Vij
  

  ---

  Guidelines for quicker answers on T-SQL question[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter