deleting duplicates

I'm having trouble deleting duplicates from enterprise manager. I get "Key
column information is insufficient or incorrect. Too many rows were affected
by update." messages.
I can run delete queries through query analyzer but that will delete the
original reocrd and duplicate. The table in question has 3 columns (all 3
columns show dup data) with no indexes and about 15 original rows duplicated.
Question 1: Why can't I delete from enterprise manager? The help file is
useless.
Question 2: What is the T-SQL for deleting the duplicate record (but leaving
the original)?
On Wed, 29 Mar 2006 20:01:01 -0800, carl wrote:
Hi Carl,
I'll address your questions in reverse order.

>Question 2: What is the T-SQL for deleting the duplicate record (but leaving
>the original)?
There is none (but see below form some kludges).
In the DELETE statement, you use a WHERE clause to tell SQL Server which
row(s) to delete. If two or more rows have the exact same data in ALL
columns, then any WHERE clause that matches one will match the other as
well. That's but one of the reasons why each table should always have at
least one PRIMARY KEY or UNIQUE constraint.

>Question 1: Why can't I delete from enterprise manager? The help file is
>useless.
Since Enterprise Manager is just a fancy front end that translates your
mouse clicks to queries, it has the same limitation as you have when
writing T-SQL statements in Query Analyzer.

>I can run delete queries through query analyzer but that will delete the
>original reocrd and duplicate. The table in question has 3 columns (all 3
>columns show dup data) with no indexes and about 15 original rows duplicated.
To delete just a single duplicated row, you can use this kludge:
SET ROWCOUNT 1
DELETE FROM MyTable
WHERE Column1 = ...
AND Column2 = ...
....
SET ROWCOUNT 0
If you want to get rid of *ALL* duplicates, rename the table, then
recreate it (don't forget to add the constraints this time!!) and move
the data back, using DISTINCT to squish the dups:
sp_rename 'MyTable', 'MyTableTMP', 'OBJECT'
go
CREATE TABLE MyTable
(Column1 int NOT NULL,
...
PRIMARY KEY (Column1, Column2)
)
go
INSERT INTO MyTable (Column1, ...)
SELECT DISTINCT Column1, ...
FROM MyTableTMP
go
DROP TABLE MyTableTMP
go
Hugo Kornelis, SQL Server MVP

Delete statement trouble

I have an Oracle 8i database with a table called GM_INV_CST. This table has the following fields:

STORE_CD NOT NULL VARCHAR2(4)
SKU_NUM NOT NULL VARCHAR2(12)
PLUS_MINUS NOT NULL VARCHAR2(1)
RCV_DT NOT NULL DATE
UNIT_CST NOT NULL NUMBER(10,2)
QTY NOT NULL NUMBER(6)
CST_TOT NOT NULL NUMBER(13,2)

I'm using the following statement to identify duplicate entries of based on SKU_NUM, STORE_CD, and PLUS_MINUS, then identify the entries with zero (0) quantities in QTY and CST_TOT:

select base.cnt,base.sku_num,base.store_cd,gm_inv_cst.plu s_minus,
gm_inv_cst.rcv_dt,gm_inv_cst.unit_cst,gm_inv_cst.q ty,gm_inv_cst.cst_tot
from gm_inv_cst,
(select count(*)as cnt,sku_num,store_cd,plus_minus
from gm_inv_cst
group by sku_num,store_cd,plus_minus
having count(*)>1) base
where gm_inv_cst.cst_tot = 0
and gm_inv_cst.qty = 0
and gm_inv_cst.sku_num=base.sku_num
and gm_inv_cst.store_cd=base.store_cd
and base.plus_minus = gm_inv_cst.plus_minus

...the problem is I can't seem to find the right syntax to create a delete statement for these records, anyone?My first observation is that you might have better luck posting an Oracle question in the Oracle (http://www.dbforums.com/f4) forum. The folks that hang out in the engine specific forums can often suggest specific features of a given engine that make the job a lot simpler than doing it the way that is required by the SQL standard.

The next observation is that you haven't specified the primary key column(s). This would help a lot to determine the simplest answer to your question.

-PatP|||If you already identified duplicate records, can't you use this query to delete them? Minor modification would then be

DELETE FROM gm_inv_cst
WHERE (plus_minus, rcv_dt, unit_cst, qty, cst_tot) IN
(SELECT g.plus_minus, g.rcv_dt, g.unit_cst, g.qty, g.cst_tot
FROM gm_inv_cst g,
(SELECT COUNT(*) AS cnt, sku_num, store_cd, plus_minus
FROM gm_inv_cst
GROUP BY sku_num, store_cd, plus_minus
HAVING COUNT(*) > 1
) base
WHERE g.cst_tot = 0
AND g.qty = 0
AND g.sku_num = base.sku_num
AND g.store_cd = base.store_cd
AND g.plus_minus = base.plus_minus
);|||I think your statement is close however it returns well over 9000 records where my original select statement only returns 463?|||Your original consolidates the duplicates, there have to be at least twice as many rows in the target table as in your result set, and quite possibly more than that!

-PatP|||It is your data, after all ... if this statement is close, enhance it a little bit and it'll be just fine :)

delete records

I am having trouble trying to figure out how to delete some records. I have
the following query:
select * from p join ppt on p.PatNo=ppt.PatNo
where p.DayTime>=ppt.Discharge
this returns 1,000 records. These are the records I want to delete from p.
I created this delete query:
delete from p
where exists (select * from p join ppt on p.PatNo=ppt.PatNo
where p.DayTime>=ppt.Discharge)
This query deletes all 16,000 records from p. Logically, what am I missing?
Thanks
DionTry this
DELETE p
FROM pjoin ppt on p.PatNo=ppt.PatNo where p.DayTime>=ppt.Discharge
"Dion" wrote:

> I am having trouble trying to figure out how to delete some records. I ha
ve
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from p
.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I missin
g?
> Thanks
> Dion
>
>|||Try this:
DELETE P
FROM P JOIN PPT
ON P.PATNO =PPT.PATNO
WHERE P.DAYTIME>=PPT.DISCHARGE
I usually wrap admin statements like this in a BEGIN TRAN then query the
table to see if the correct results are obtained if so COMMIT TRAN if not
ROLLBACK TRAN.
HTH
Jerry
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:DB2A229F-F7E6-4107-A646-F4C915DDE1B8@.microsoft.com...
>I am having trouble trying to figure out how to delete some records. I
>have
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from
> p.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I
> missing?
> Thanks
> Dion
>
>|||On Fri, 30 Sep 2005 14:45:02 -0700, Dion wrote:
>I am having trouble trying to figure out how to delete some records. I hav
e
>the following query:
>select * from p join ppt on p.PatNo=ppt.PatNo
>where p.DayTime>=ppt.Discharge
>this returns 1,000 records. These are the records I want to delete from p.
>I created this delete query:
>delete from p
>where exists (select * from p join ppt on p.PatNo=ppt.PatNo
>where p.DayTime>=ppt.Discharge)
>This query deletes all 16,000 records from p. Logically, what am I missing?[/color
]
Hi Dion,
You didn't correlate the subquery to the main query. The subquery runs
by itself, so it will return the same result (1,000 rows) for each row
in the outer query - and that means that the EXISTS is true for each row
in p.
DELETE FROM p
WHERE EXISTS (SELECT *
FROM ppt
WHERE p.PetNo = ppt.PatNo
AND p.DayTime >= ppt.Discharge)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try:
DELETE
FROM P
WHERE P.<PK column name> IN (SELECT P.<PK COlum name>
FROM P
INNER
JOIN PPT
ON
P.PatNo = ppt.PatNo
WHERE P.DayTime >=
PPT.DIscharge )
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:DB2A229F-F7E6-4107-A646-F4C915DDE1B8@.microsoft.com...
> I am having trouble trying to figure out how to delete some records. I
have
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from
p.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I
missing?
> Thanks
> Dion
>
>

Delete record selection question

I'm having trouble with the below sql command. What I'm trying todo is to delete records from tblPhotoHeader table where there are nocorresponding child records in tblPhoto.
The select statement works correctly, I'm just not sure about how toapply the syntax to correctly select the records in the deletestatement.
Any help from the experts here would be helpful.
Thanks
Tom
---------------------
DELETE FROM tblPhotoHeader
WHERE Exists
(SELECT tblPhotoHeader.photoid, photoOrderID,tblPhoto.photoType
FROM tblPhotoHeader LEFT OUTER JOIN
tblPhoto ON tblPhotoHeader.photoID = tblPhoto.photoID
WHERE (tblPhotoHeader.photoOrderID = 143)AND (tblPhoto.photoType IS NULL))
EXISTS will return TRUE of FALSE. So if your SELECT statement returnsdate the EXISTS will return TRUE. Then your SQL Statement wouldevaluate as:
DELETE FROM tblPhotoHeader WHERE TRUE which doesnt make sense. So you need to do something like :
DELETE FROM tblPhotoHeader
WHERE tblPhotoHeader.photoid IN ( (SELECT
tblPhotoHeader.photoid
FROM
tblPhotoHeader
LEFT OUTER JOIN tblPhoto
ONtblPhotoHeader.photoID = tblPhoto.photoID
WHERE (tblPhotoHeader.photoOrderID = 143)AND (tblPhoto.photoType IS NULL))