Solved

first select, then change query to allow delete

Posted on 2010-11-18
12
248 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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