Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

first select, then change query to allow delete

Posted on 2010-11-18
12
Medium Priority
?
296 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Technology Partners: 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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

636 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