Solved

first select, then change query to allow delete

Posted on 2010-11-18
12
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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

 

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 41

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
 

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 41

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:Ephraim Wangoya
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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