deleting duplicate rows

• oldgoat

  SSCrazy

  Points: 2344

  More actions

  April 1, 2009 at 4:31 am

  #207781

  thing is, I'm getting syntax errors every time I try via a join

  select u1.*

  FROM cn_users u1

  join cn_users u2 on u1.userid = u2.userid

  and u1.id > u2.id

  returns 227 rows with no problem

  as soon as I try

  delete u1.*

  FROM cn_users u1

  join cn_users u2 on u1.userid = u2.userid

  and u1.id > u2.id

  I get

  incorrect syntax near '*'

  yet this should work, going by everything I've read.

  I've certainly had it working in the past, but can't find the script file at present!

• Mark Cowne

  One Orange Chip

  Points: 26865

  More actions

  April 1, 2009 at 4:43 am

  #969711

  delete u1

  FROM cn_users u1

  join cn_users u2 on u1.userid = u2.userid

  and u1.id > u2.id

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• oldgoat

  SSCrazy

  Points: 2344

  More actions

  April 1, 2009 at 4:47 am

  #969712

  Thanks.

  I was sure I'd tried that one, but what the.... it worked.

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9119

  More actions

  April 1, 2009 at 2:18 pm

  #970210

  * is required only when you are using SELECT operation.

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers