Help with a large delete

I have a table that is part a merge publication that I need to delete 3
million rows from which will leave about a million in the table. I plan to
just insert a list of the id's to go in a temporary table and then just do
delete where id in table X.
Another way that has been suggested to me is to place a copy of the records
that I want to keep into another table and then truncate the main table and
then put the ones I want back in. Would this method work due to the table
being part of a merge publication, as I presume that the truncate would not
happen on the server that is subscribed to this publication.
I'd do the truncate and reinsert as your transaction log will likely grow
absolutely huge the other way. Not to say it won't grow huge the first way.
keep in mind that in order to do the truncate you will need to drop the
subscriptions and publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:F3DE3D1A-A230-4E87-B23D-79CA1B2F8460@.microsoft.com...
> I have a table that is part a merge publication that I need to delete 3
> million rows from which will leave about a million in the table. I plan to
> just insert a list of the id's to go in a temporary table and then just do
> delete where id in table X.
> Another way that has been suggested to me is to place a copy of the
records
> that I want to keep into another table and then truncate the main table
and
> then put the ones I want back in. Would this method work due to the table
> being part of a merge publication, as I presume that the truncate would
not
> happen on the server that is subscribed to this publication.
|||Will it only grow huge on the server that I do the delete on or will it grow
huge on both servers?
"Hilary Cotter" wrote:

> I'd do the truncate and reinsert as your transaction log will likely grow
> absolutely huge the other way. Not to say it won't grow huge the first way.
> keep in mind that in order to do the truncate you will need to drop the
> subscriptions and publications.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:F3DE3D1A-A230-4E87-B23D-79CA1B2F8460@.microsoft.com...
> records
> and
> not
>
>

Help with a delete statement

Wondering if someone can show me the correct syntax of a delete
statement where I want to delete rows from table 'A' where say column1
of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
2 of table 'B'. Working within a procedure and would like to do
something cleaner than having to create a cursor. I'm sure there
example but not sure exactly what to search for!
Thanks,
JeffThis should give you something to think about.
DELETE FROM A
WHERE EXISTS
(SELECT * FROM B
WHERE A.col1 = B.col1
AND A.col2 = B.col2)
Roy Harvey
Beacon Falls, CT
On 4 Jan 2007 15:20:55 -0800, "jeff" <jeffa@.telect.com> wrote:
>Wondering if someone can show me the correct syntax of a delete
>statement where I want to delete rows from table 'A' where say column1
>of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
>2 of table 'B'. Working within a procedure and would like to do
>something cleaner than having to create a cursor. I'm sure there
>example but not sure exactly what to search for!
>Thanks,
>Jeff

Help with a delete statement

Wondering if someone can show me the correct syntax of a delete
statement where I want to delete rows from table 'A' where say column1
of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
2 of table 'B'. Working within a procedure and would like to do
something cleaner than having to create a cursor. I'm sure there
example but not sure exactly what to search for!
Thanks,
Jeff
This should give you something to think about.
DELETE FROM A
WHERE EXISTS
(SELECT * FROM B
WHERE A.col1 = B.col1
AND A.col2 = B.col2)
Roy Harvey
Beacon Falls, CT
On 4 Jan 2007 15:20:55 -0800, "jeff" <jeffa@.telect.com> wrote:

>Wondering if someone can show me the correct syntax of a delete
>statement where I want to delete rows from table 'A' where say column1
>of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
>2 of table 'B'. Working within a procedure and would like to do
>something cleaner than having to create a cursor. I'm sure there
>example but not sure exactly what to search for!
>Thanks,
>Jeff
|||Here's an interesting twist of the DELETE statement syntax that I ran across
not too long ago...
DELETE FROM TableA FROM TableB
WHERE TableA.Column1 = TableB.Column1 AND
TableA.Column2 = TableB.Column2 AND
TableA.Column3 = TableB.Column3
The double FROMs look weird, but it's valid syntax and it even works.
Regards,
Jerry
"jeff" wrote:

> Wondering if someone can show me the correct syntax of a delete
> statement where I want to delete rows from table 'A' where say column1
> of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
> 2 of table 'B'. Working within a procedure and would like to do
> something cleaner than having to create a cursor. I'm sure there
> example but not sure exactly what to search for!
> Thanks,
> Jeff
>

Help with a delete statement

Wondering if someone can show me the correct syntax of a delete
statement where I want to delete rows from table 'A' where say column1
of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
2 of table 'B'. Working within a procedure and would like to do
something cleaner than having to create a cursor. I'm sure there
example but not sure exactly what to search for!
Thanks,
JeffThis should give you something to think about.
DELETE FROM A
WHERE EXISTS
(SELECT * FROM B
WHERE A.col1 = B.col1
AND A.col2 = B.col2)
Roy Harvey
Beacon Falls, CT
On 4 Jan 2007 15:20:55 -0800, "jeff" <jeffa@.telect.com> wrote:

>Wondering if someone can show me the correct syntax of a delete
>statement where I want to delete rows from table 'A' where say column1
>of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
>2 of table 'B'. Working within a procedure and would like to do
>something cleaner than having to create a cursor. I'm sure there
>example but not sure exactly what to search for!
>Thanks,
>Jeff|||Here's an interesting twist of the DELETE statement syntax that I ran across
not too long ago...
DELETE FROM TableA FROM TableB
WHERE TableA.Column1 = TableB.Column1 AND
TableA.Column2 = TableB.Column2 AND
TableA.Column3 = TableB.Column3
The double FROMs look weird, but it's valid syntax and it even works.
Regards,
Jerry
"jeff" wrote:

> Wondering if someone can show me the correct syntax of a delete
> statement where I want to delete rows from table 'A' where say column1
> of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
> 2 of table 'B'. Working within a procedure and would like to do
> something cleaner than having to create a cursor. I'm sure there
> example but not sure exactly what to search for!
> Thanks,
> Jeff
>

Help with a delete query

Hi

i need help with this task i can't think how to do better.
I have a table with MyField1, MyField2, Cost, SomeOtherField

For each couple of MyField1, MyField2 i have several rows.
I need to delete some rows so that:
- for each couple of MyField1, MyField2 i have at most @.MaxNumber rows remaining and
- for each pair MyField1,MyField2 the Cost in the remaining rows is never more than a certain coefficient @.K times the cost of the least costly row of the pair (each MyField1,MyField2 has its own minimum cost).

I did this easily with a Cursor but i needed better performances.
I tried with this:

delete from MyTable from MyTable as p1 where
0 = (select (1 + sign(@.MaxNumber -1-count(*))) * (1 + sign(min(p2.Cost) * @.K- p1.Cost) )
from MyTable p2 where p2.MyField1 = p1.MyField1 and p2.MyField2 = p1.MyField2 and p2.Cost< p1.Cost)

What's happening here ? for each row i count how many rows are there with the same MyFields that have cost less than @.K*MinValue , i multiply the two difference between the value i need and the real value and only when one of these values are zero i know that the product is zero and so i can choose the row to be deleted. Note that the double "FROM" in the query is no mistake.
This is terribly involuted, it works better than the cursor but i am sure there must be a better way to do this !!

Could You please help me ?

Thankx

Wentuhi,

can you post a sample data?|||

Try This...

Code Snippet

Delete

From mytable

From mytable as Main

Join

(

Select p1.*

From mytable p1

Join mytable p2 Onp2.MyField1 = p1.MyField1

and p2.MyField2 = p1.MyField2

and p2.Cost< p1.Cost

Group By

p1.MyField1, p1.MyField2, p1.Cost

Having

(1 + sign(@.MaxNumber -1-count(*))) * (1 + sign(min(p2.Cost) * @.K- p1.Cost)) =0

) as Sub

OnSub.MyField1 = Main.MyField1 and Sub.MyField2 = Main.MyField2 and Sub.Cost = Main.Cost

|||

Manni's query looks better; nonetheless, I put together a mockup. The mockup used the SMALL_ITERATOR and DBO.RAND() objects that can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

The table definition that I used is:

create table dbo.myTable
( rid integer,
myField1 integer,
myField2 integer,
cost numeric (9,2),

constraint pk_myTable primary key (myField1, myField2, cost, rid)
)
go

insert into dbo.myTable
select iter,
1 + (iter/35),
5*dbo.rand(),
33*dbo.rand() + 33*dbo.rand()
+ 34*dbo.rand()
from small_iterator (nolock)
where iter <= 40
select * from dbo.myTable

And the query is:

--select * from myTable where myField1 = 1 and myField2 = 1 order by cost
/*
rid myField1 myField2 cost
-- -- --
8 1 1 32.97
6 1 1 42.40
25 1 1 44.39
3 1 1 50.12
34 1 1 56.03
5 1 1 58.37
17 1 1 64.41
13 1 1 65.66
24 1 1 75.05
*/

declare @.maxRows integer set @.maxRows = 4
declare @.boundary numeric (6,2) set @.boundary = 1.5

;with theMinCost as
( select myField1,
myField2,
@.boundary * min(cost) as maxCost
from myTable
group by myField1, myField2
), seqTable as
( select row_number () over
( partition by myField1, myField2
order by cost, rid
) as seq,
myField1,
myField2,
cost,
rid
from myTable
), candidates as
( select a.myField1,
a.myField2,
a.seq,
a.cost,
b.maxCost,
a.rid
from seqTable a
inner join theMinCost b
on a.myField1 = b.myField1
and a.myField2 = b.myField2
and ( a.seq > @.maxRows or
a.cost > b.maxCost
)
)

--select * from theMinCost where myField1=1 and myField2=1
/*
myField1 myField2 maxCost
-- -- -
1 1 49.4550
*/

/*
select * from candidates
where myField1 = 1
and myField2 = 1

myField1 myField2 seq cost maxCost rid
-- -- -- -- --
1 1 4 50.12 49.4550 3
1 1 5 56.03 49.4550 34
1 1 6 58.37 49.4550 5
1 1 7 64.41 49.4550 17
1 1 8 65.66 49.4550 13
1 1 9 75.05 49.4550 24
*/

delete from myTable
from myTable a
inner join candidates b
on a.myField1 = b.myField1
and a.myField2 = b.myField2
and a.rid = b.rid

select * from myTable where myField1 = 1 and myField2 = 1 order by cost

/*
rid myField1 myField2 cost
- -- -- --
8 1 1 32.97
6 1 1 42.40
25 1 1 44.39
*/

|||Wentu,

If you are using SQL Server 2005, try this. It may be efficient and relatively easy to understand and maintain. (I'm using Kent's table structure.)

Code Snippet

with WithMins(rid, myField1, myField2, minCost, cost) as (
select
rid,
myField1,
myField2,
min(cost) over (
partition by myField1, myField2
) as minCost,
cost
from myTable
), Cheaps(rid, myField1, myField2, cost, minCost, n) as (
select
rid,
myField1,
myField2,
cost,
minCost,
row_number() over (
partition by myField1, myField2
order by myField1, myField2, cost
-- cost <= @.K*minCost rows are numbered lowest
) as n
from WithMins
)
delete from Cheaps
where cost > @.K*minCost
or n > @.MaxNumber

Steve Kass
Drew University
http://www.stevekass.com
|||:-) (Admiring the work)|||Hi all

let me understand all Your codes, try them , and i'll let You know.
I want to thankx all of You for Your efforts, time and help !
c ya soon

Wentu

Help to deleting n numbers of records

Hi
One of our users have by mistake created a number of dublicate records in
the database. Now I need to delete the "faulty" records from the table and
just keep one of them. The records are linked to a customer table, so I have
a Recordid column in the table that I can use to group on. E.g. if I in the
table have 10 records with RecordID 1, I need to delete 9 of them and keep
1, 8 records with RecordID 2 I need to delete 7 and keep 1 etc.
I'll have to search the customer table to find the recordID's that is used
to link to the child table, so my plan is to use a cursor to find these and
put them into a variable. I'll then use this to find the record(s) in the
child table, get the number of records and then either delete them one by
one until I've only 1 pr. RecordID left or use SELECT Top x based on the
number of records found with each recordid and then delete all but 1 record.
It's not a huge number of records I need to delete so from a practical
and/or performance point of wiev it's not critical how I do it. It's more
that I'm currious to hear if my approach is the best one or if any of you
have any other ideas?
TIA
Regards
SteenTo answer this properly we'll need to know the keys and constraints in
your tables. Please post DDL (CREATE TABLE) and some sample data
(INSERTs) if you want help with the actual code.
You missed out one critical step from your solution: Add a new unique
constraint so that this can't happen again.
David Portas
SQL Server MVP
--|||Hi
First of all, it's a vendor application, so I can't change anything in the
database or application. I this case it's not a problem though, since it's
ok to create several records as they did, but in this case it's just because
they had some problems with a printer and therefore thay ran a wizard
several times which generated a number of records that where baiscally same.
It's these records thay now want to get deleted.
The 2 tables that's involved is called Lejer and Note.
CREATE TABLE [Lejer] (
[EjendomNr] [EjendomNr] NOT NULL ,
[LejemaalNr] [LejemaalNr] NOT NULL ,
[LejerNr] [LejerNr] NOT NULL ,
[LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
....and about 200 more column definitions
CREATE TABLE [Note] (
[NoteID] [RecordID] NOT NULL ,
[Tabelnavn] [TabelNavn] NOT NULL ,
[RecordID] [RecordID] NOT NULL ,
[Dato] [Dato] NOT NULL ,
[NoteType] [KodeId] NOT NULL ,
....and some more column definitions.
The fields that links the tables are Lejer.LejerID and Note.RecordID.
Below is ans example of sample data
Lejer:
Ejendomnr Lejemaalnr Lejernr LejerID
1 1 1 1000
1 2 2 1001
1 3 3 1002
2 1 1 1003
2 2 2 1004
3 1 1 1005
3 2 2 1006
Note
NoteID RecordID NoteType
1 1000 29000
2 1000 29000
3 1000 29000
4 1000 29000
5 1001 29000
6 1001 29000
7 1001 29000
8 1002 29000
9 1002 29000
10 1002 29000
11 1003 29000
12 1003 29000
I'd like to find the notes where the type is e.g. 29000 and are linked a
record in the Lejer table with the Ejendomnr of e.g. 1.
and then delete all notes but 1.
In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note records with NoteID 1 to 10. Out of these I'd like to delete 3 of the
ones with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with
RecordID 1002.
As mentioned in my original post, I could get all the RecordID's into a
cursor and then e.g. count the number of occurences for each of them and
then do a delete n-1 times. I just don't know if that's the smartest way to
do it or if there's a better approach?
Regards
Steen
David Portas wrote:
> To answer this properly we'll need to know the keys and constraints in
> your tables. Please post DDL (CREATE TABLE) and some sample data
> (INSERTs) if you want help with the actual code.
> You missed out one critical step from your solution: Add a new unique
> constraint so that this can't happen again.
> --
> David Portas
> SQL Server MVP|||How about
Delete Note
Where NoteType = '29000'
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Here I'm assuming that a given Note.RecordId must exist in Lejer.LejerId.
However, it is possible that this is not the case and you want to ensure tha
t
the record does exist in the Lejer table then you could add an Exists clause
like so:
Delete Note
Where NoteType = '29000'
And Exists(
Select *
From Lejer As L1
Where L1.LegerId = Note.RecordId
)
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Obviously, you should execute this code carefully to ensure that it is produ
ce
the results you want before you commit against production data.
HTH
Thomas
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Hi
> First of all, it's a vendor application, so I can't change anything in the
> database or application. I this case it's not a problem though, since it's
ok
> to create several records as they did, but in this case it's just because
they
> had some problems with a printer and therefore thay ran a wizard several t
imes
> which generated a number of records that where baiscally same. It's these
> records thay now want to get deleted.
> The 2 tables that's involved is called Lejer and Note.
>
> CREATE TABLE [Lejer] (
> [EjendomNr] [EjendomNr] NOT NULL ,
> [LejemaalNr] [LejemaalNr] NOT NULL ,
> [LejerNr] [LejerNr] NOT NULL ,
> [LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> .....and about 200 more column definitions
>
> CREATE TABLE [Note] (
> [NoteID] [RecordID] NOT NULL ,
> [Tabelnavn] [TabelNavn] NOT NULL ,
> [RecordID] [RecordID] NOT NULL ,
> [Dato] [Dato] NOT NULL ,
> [NoteType] [KodeId] NOT NULL ,
> ....and some more column definitions.
> The fields that links the tables are Lejer.LejerID and Note.RecordID.
>
> Below is ans example of sample data
> Lejer:
> Ejendomnr Lejemaalnr Lejernr LejerID
> 1 1 1 1000
> 1 2 2 1001
> 1 3 3 1002
> 2 1 1 1003
> 2 2 2 1004
> 3 1 1 1005
> 3 2 2 1006
> Note
> NoteID RecordID NoteType
> 1 1000 29000
> 2 1000 29000
> 3 1000 29000
> 4 1000 29000
> 5 1001 29000
> 6 1001 29000
> 7 1001 29000
> 8 1002 29000
> 9 1002 29000
> 10 1002 29000
> 11 1003 29000
> 12 1003 29000
>
> I'd like to find the notes where the type is e.g. 29000 and are linked a
> record in the Lejer table with the Ejendomnr of e.g. 1.
> and then delete all notes but 1.
> In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note
> records with NoteID 1 to 10. Out of these I'd like to delete 3 of the ones
> with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with Record
ID
> 1002.
> As mentioned in my original post, I could get all the RecordID's into a cu
rsor
> and then e.g. count the number of occurences for each of them and then do
a
> delete n-1 times. I just don't know if that's the smartest way to do it or
if
> there's a better approach?
> Regards
> Steen
>
> David Portas wrote:
>|||Hi Thomas
Thanks for you input. That was another way of doing it than I had in my
mind. I'll try it out in my test db.
Regards
Steen
Thomas Coleman wrote:
> How about
> Delete Note
> Where NoteType = '29000'
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Here I'm assuming that a given Note.RecordId must exist in
> Lejer.LejerId. However, it is possible that this is not the case and
> you want to ensure that the record does exist in the Lejer table then
> you could add an Exists clause like so:
> Delete Note
> Where NoteType = '29000'
> And Exists(
> Select *
> From Lejer As L1
> Where L1.LegerId = Note.RecordId
> )
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Obviously, you should execute this code carefully to ensure that it
> is produce the results you want before you commit against production
> data.
> HTH
>
> Thomas
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...