Solved

first select, then change query to allow delete

Posted on 2010-11-18
12
258 Views
Last Modified: 2012-05-10
want to do select, top id (int) from orders where orderid(varchar) is duplicate


I want to be able to change that query so I can delete the row with that top id
0
Comment
Question by:rgb192
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 2

Expert Comment

by:joshgeake
ID: 34168881
you mean you have duplicate rows in a database that you want to identify and remove?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34168975
try this.
delete t1 from yourtable t1 where id <> (select MIN(id) from yourtable t2 where t1.orderid = t2.orderid)

Open in new window

0
 
LVL 2

Expert Comment

by:joshgeake
ID: 34168986
or if you want an easy but messy solution...
select distinct *
into table_temp
from table

truncate table

insert table
select * from table_temp

drop table_temp

Open in new window

0
 

Author Comment

by:rgb192
ID: 34168992
my table is named a

select t1 from a t1 where id <> (select MIN(id) from a t2 where t1.orderid = t2.orderid)



Msg 207, Level 16, State 1, Line 1
Invalid column name 't1'.


I will change the 'select' to a 'delete' when I know that it works
because I dont want to lose my data
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34169020
try this to see what are the records that are going to be deleted.

select * from a t1 where id <> (select MIN(id) from a t2 where t1.orderid = t2.orderid)
0
 
LVL 2

Expert Comment

by:joshgeake
ID: 34169078
@Sharath_123

If the id's are duped will they show up on a MIN(id)?

I'd have thought you'll want to run something like...select * from a having count(id) > 1

to eliminate them I'd run the ... select distinct * into a_temp from a...
and then recreate a from a_temp
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:rgb192
ID: 34169110
when I run select query
select * from a t1 where id <> (select MIN(id) from a t2 where t1.orderid = t2.orderid) order by orderid desc    (i added order by orderid desc)

I get some rows that do not have duplicate orderid with other rows

CREATE TABLE [dbo].[a](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [orderdate] [varchar](100) NULL,
      [saleschannel] [varchar](100) NULL,
      [fulfilmentchannel] [varchar](100) NULL,
      [orderid] [varchar](100) NULL,
      [productdetails] [varchar](400) NULL,
      [qty] [varchar](100) NULL,
      [asin] [varchar](100) NULL,
      [sku] [varchar](100) NULL,
      [contactbuyer] [varchar](1000) NULL,
      [shippingservice] [varchar](100) NULL,
      [status] [varchar](100) NULL,
      [refundinfo] [varchar](100) NULL,
      [totalprice] [varchar](100) NULL
) ON [PRIMARY]
0
 

Author Comment

by:rgb192
ID: 34169126
i can delete top, min, max id
just as long as I delete
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34169165
In the original question, you mentioned that orderid has duplicates. From your table definition also, there is no way of having duplicates on id column.

What I am doing is my query is, identifying the duplicate records for orderid except the first record ( the record with min id for an orderid). once those records are identified, you can delete those records.

>> I get some rows that do not have duplicate orderid with other rows

provide some sample set
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34170453
Try

select * from a t1
where exists (select 1 from a t2
              where t2.orderid = t1.orderid
              and t2.id > t1.id)
0
 
LVL 2

Expert Comment

by:joshgeake
ID: 34172176
@Sharath_123 - agreed, if id is an IDENTITY it couldn't have a dupe.

Hence, @rgb192 - what is being duplicated? And as @Sharath_123 asked, you should really supply us with a row you think is duped.
0
 

Author Closing Comment

by:rgb192
ID: 34176869
works.... thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now