An unusual T-SQL query 2

[phzero]

Hi all,

I'm using SQL Server 7.0. I have two identical entries in my SQL table. Does anyone out there have an sql query, or know of a way to delete one of two identical entries. To display only one of the two is OK when using the DISTINCT keyword, but I'd like to remove the second one. Any comments will be appreciated. Have a good day.

 

[SPIKEY2]

Why don't you SELECT * INTO <newtable> GROUP BY <columns that are duplicates> This would yeild a table without dups. Replace the old table with the new one.

If you can't replace the table and have to keep it in place try Selecting into a new table with the group by or distinct and using the key column, delete from the original table WHERE NOT IN <the new table>

 

[phzero]

Great idea Spikey2. Haven't thought of that. I think I had my mind so set on the delete..where statement that I forgot about the rest of it. Just shows you, think too much of one thing and you'll soon become narrow-minded. I know it'll work, just looking at the code. Thanks again.

 

[CliveW]

no problem. I would advise don't drop the table as you will loose the relationships and constraints. If not too large delete the contents and insert new table.

 

[tlbroadbent]

Spikey2's solution will not work unless all the columns of the table are listed in the Group By clause.

You can use Select Distinct * Into NewTable. It is simpler and it will work.

After you've cleaned up your data, I recommend creating a Primary Key or Unique constraint on the table to prevent more duplicates. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[phzero]

Thanks for your input Terry. After having tried the approach I realised that all columns would have to be listed, I eventually came to the exact query you just posted. Thanks again for your input. Thanks for the FAQ, I'll check it out. I now have my PK's in place. Keep well.

 

[SPIKEY2]

You are so right Terry, it's my Foxpro background showing. In Fox you don't have to have all the columns present to do a group by and so there is little difference between a distinct and group by (except group by is faster).

Forgive me I have only been using SQL Server for 2 months. I think I'll leave the replying to you guys.

 

[tlbroadbent]

Don't you dare leave the replying to anyone. Tek-Tips is great because people participate and answer questions. I can't believe how much I've learned either by reading other member posts or researching problems and questions. We all benefit. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLSister]

Besides that spikey your response did lead him to the solution.

 

[SQLBill]

Spikey,

We can't all be right all the time. I've made my share of mistakes and some of them have been pretty obvious ones (although they weren't to me at the time). If someone learns from your post, then it was worth it and both the poster AND you learned from this one.

-SQLBill

 

[lespaul]

Terry - If you are still learning stuff reading tips I guess it proves you never know everything! I'm always impressed by your responses and solutions, they are usually very succinct. As much as I think I know, I come to tek-tips and become very humble when I realize how little I really do know. Keep up your great posting! Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[sguslan]

I concur with pros(Terry, SQLBill and SQLSister). We all have room for improvement and learning.

Lespaul we need your answers and questions !

I learn something every day. In fact i instructed my DBAs to visit this site on daily basis to learn ...

 

[phzero]

Hi all,

You guys are simply the greatest. If only the world community could be like this. Have a good one. Cheers.