?
Solved

first select, then change query to allow delete

Posted on 2010-11-18
12
Medium Priority
?
291 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

762 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