how to delete duplicate col.

Question

how to delete duplicate col.

anzz

SSCertifiable

Points: 5503
More actions
July 18, 2012 at 11:50 am

#261399

I have a table, with duplicate column,
how can i delete one row and keeping the another row.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Is it the row or the column that is of concern?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

anzz SSCertifiable Points: 5503 More actions · Answer 2

anzz

SSCertifiable

Points: 5503

July 18, 2012 at 11:55 am

#1514406

it is row.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Here is an article that should help.

http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

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? 🙂

~ 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

anzz SSCertifiable Points: 5503 More actions · Answer 5

anzz

SSCertifiable

Points: 5503

July 18, 2012 at 12:05 pm

#1514419

Can I get T-sql statement.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Danzz (7/18/2012)
Can I get T-sql statement.

Based on what? We don't have anything useful from you to provide you with any code that would be useful to you. If you provide us with the DDL for the table, some sample data, and expected results I'm sure we could come up with something quickly.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Danzz (7/18/2012)
Can I get T-sql statement.

Actually if you read the article it is really quite simple.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Sean Lange (7/18/2012)
Danzz (7/18/2012)
Can I get T-sql statement.
Actually if you read the article it is really quite simple.

Thank you.:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Lynn Pettis (7/18/2012)
Danzz (7/18/2012)
Can I get T-sql statement.
Based on what? We don't have anything useful from you to provide you with any code that would be useful to you. If you provide us with the DDL for the table, some sample data, and expected results I'm sure we could come up with something quickly.

Agree with Lynn. It is for this reason that an article reference with a clear example was given.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lokesh Vij SSChampion Points: 10836 More actions · Answer 11

cool...Thanks Jason.

Much appreciated!

- 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

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

SQLRNNR (7/18/2012)
Sean Lange (7/18/2012)
Danzz (7/18/2012)
Can I get T-sql statement.
Actually if you read the article it is really quite simple.
Thank you.:-D

You're welcome. Not sure how I never found that one before. Great article on a frequent topic!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

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).

I did check with a friend on the Oracle side that confirmed it would work if it is ansi compliant.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events