By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,483 IT Pros & Developers. It's quick & easy.

Delete Records From a Table Using Records in Another Table.

P: n/a
I have been searching many postings and I cant seem to find anyone
that has this answer so I decided to post. I am using SQL
(Transact-SQL).If I have 2 tables with columns
acct_num,activity_date,and pay_amt and I want to delete one instance
of a record in table 1 for every instance of that record in table 2
how could I do that. For example.

Table 1
-----------
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00

Table 2
-----------
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00

I need a delete statement that will find 2 of the 5 records(It doesn't
matter which 2) and delete them.
Leaving table one looking like this.
Table 1
-----------
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00

How can I do this??
Jul 20 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On 20 Aug 2004 11:28:35 -0700, Philip Mette wrote:
I have been searching many postings and I cant seem to find anyone
that has this answer so I decided to post. I am using SQL
(Transact-SQL).If I have 2 tables with columns
acct_num,activity_date,and pay_amt and I want to delete one instance
of a record in table 1 for every instance of that record in table 2
how could I do that. For example.

Table 1
-----------
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00

Table 2
-----------
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00

I need a delete statement that will find 2 of the 5 records(It doesn't
matter which 2) and delete them.
Leaving table one looking like this.
Table 1
-----------
acct activity_date pay_amt
123 5/1/2004 50.00
123 5/1/2004 50.00
123 5/1/2004 50.00

How can I do this??


Oy vey. There's no other fields in your table? No primary key? If you had a
primary key it wouldn't be so bad. But without a primary key, there's no
way to delete one row and leave another alone.

The nostandard MS SQL Server feature SET ROWCOUNT could get you out of it
if you are deleting from only one group at a time (say, iterating through
the groups in table2 on the client side or with a cursor). But that's very
ugly.

Tables NEED a primary key. Mr. Celko constantly makes this point, and he's
right.

With a primary key, if the tables were defined as

CREATE TABLE Table1 (
TransID int identity(4309,1) primary key,
acct char(3),
activity_date datetime,
pay_amt money
);

CREATE TABLE Table2 (
TransID int identity(2389,1) primary key ,
acct char(3),
activity_date datetime,
pay_amt money
);

INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table1 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)

INSERT INTO Table2 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)
INSERT INTO Table2 (acct,activity_date,pay_amt) values(123,'5/1/2004',50)

then you can use this monstrosity to delete as many rows from table1 as
there are matching rows in table2:

DELETE FROM Table1
WHERE Table1.TransID IN
(
SELECT TransID FROM
(
SELECT X.TransID,X.acct,X.activity_date,X.pay_amt,COUNT(Y .TransID) num
FROM Table1 X
INNER JOIN Table1 Y
ON X.acct=Y.acct
AND X.activity_date=Y.activity_date
AND X.pay_amt=Y.PAY_AMT
WHERE X.TransID >= Y.TransID
GROUP BY X.TransID,X.acct,X.activity_date,X.pay_amt
) T1
WHERE T1.num <= (
SELECT COUNT(*) FROM Table2 T2
WHERE T1.acct=T2.acct
AND T1.activity_date=T2.activity_date
AND T1.pay_amt=T2.PAY_AMT
)
)

I'm pretty sure this can be simplified into at least one fewer derived
table, but my head hurts from doing this much.
Jul 20 '05 #2

P: n/a
[posted and mailed, please reply in news]

Philip Mette (ph******@msn.com) writes:
I have been searching many postings and I cant seem to find anyone
that has this answer so I decided to post. I am using SQL
(Transact-SQL).If I have 2 tables with columns
acct_num,activity_date,and pay_amt and I want to delete one instance
of a record in table 1 for every instance of that record in table 2
how could I do that. For example.
...
I need a delete statement that will find 2 of the 5 records(It doesn't
matter which 2) and delete them.
Leaving table one looking like this.


As Ross Presser noted there is no primary key in your table, and tables
are supposed to have primary keys to uniquely identify each row. When you
have not, and this causes problems you are out in the wilderness and have
to apply funny tricks to get back home.

Here is a solution that does not create extra table. It is non-tested,
since you did not include CREATE TABLE statements and INSERT statements
for your sample data.

DECLARE @accno int, @actdate datetime, @payamt money, @cnt int

DECLARE cur INSENSITIVE CURSOR FOR
SELECT DISTINCT accno, actdate, payamt
FROM tbl1

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @accno, @actdate, @payamt
IF @@fetch_status <> 0
BREAK

SELECT @cnt = (SELECT COUNT(*) FROM tbl1
WHERE accno = @accno AND @actdate = actdate
AND payamt = @payamt) -
(SELECT COUNT(*) FROM tbl2
WHERE accno = @accno AND @actdate = actdate
AND payamt = @payamt)
IF @cnt <= 0
CONTINUE

SET ROWCOUNT @cnt

DELETE tbl1
WHERE accno = @accno AND @actdate = actdate AND payamt = @payamt

SET ROWCOUNT 0
END

DEALLOCATE cur

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
On Fri, 20 Aug 2004 21:33:53 +0000 (UTC), Erland Sommarskog wrote:
Philip Mette (ph******@msn.com) writes:
I have been searching many postings and I cant seem to find anyone
that has this answer so I decided to post. I am using SQL
(Transact-SQL).If I have 2 tables with columns
acct_num,activity_date,and pay_amt and I want to delete one instance
of a record in table 1 for every instance of that record in table 2
how could I do that. For example.
...
I need a delete statement that will find 2 of the 5 records(It doesn't
matter which 2) and delete them.
Leaving table one looking like this.


As Ross Presser noted there is no primary key in your table, and tables
are supposed to have primary keys to uniquely identify each row. When you
have not, and this causes problems you are out in the wilderness and have
to apply funny tricks to get back home.

Here is a solution that does not create extra table. It is non-tested,
since you did not include CREATE TABLE statements and INSERT statements
for your sample data.


[snip]

Very clever. I still don't feel comfortable enough with cursors to write
such code off the cuff. :)

One minor point: you have it computing the difference between count(tbl1.*)
and count(tbl2.*), and deleting that number -- so if tbl1 starts with five
and tbl2 with two, it deletes three. But rereading his original request, I
think he wanted two deleted -- and that's how I wrote my monstrosity.
Jul 20 '05 #4

P: n/a
Ross Presser (rp******@imtek.com) writes:
Very clever. I still don't feel comfortable enough with cursors to write
such code off the cuff. :)
Clever? Quite ugly, but it was an ugly problem.
One minor point: you have it computing the difference between
count(tbl1.*) and count(tbl2.*), and deleting that number -- so if tbl1
starts with five and tbl2 with two, it deletes three. But rereading his
original request, I think he wanted two deleted -- and that's how I
wrote my monstrosity.


Do I have to say it again? When you have a problem like this, please
include:
o CREATE TABLE statements for your tables.
o INSERT statements with the sample data.
o The expected result.

This makes it very easy to cut and paste and from that produce a tested
solution. If you don't provide that, you are likely to get an untested
solution, because people who answer questions in these newsgroups do this
for fun, and not because they like typing. So you who have the problem
do the boring mechanical work, and we take care of the fun part: the
intellectual challenge.

So, there! :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

P: n/a
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Ross Presser (rp******@imtek.com) writes:
Very clever. I still don't feel comfortable enough with cursors to write
such code off the cuff. :)


Clever? Quite ugly, but it was an ugly problem.
One minor point: you have it computing the difference between
count(tbl1.*) and count(tbl2.*), and deleting that number -- so if tbl1
starts with five and tbl2 with two, it deletes three. But rereading his
original request, I think he wanted two deleted -- and that's how I
wrote my monstrosity.


Do I have to say it again? When you have a problem like this, please
include:
o CREATE TABLE statements for your tables.
o INSERT statements with the sample data.
o The expected result.

This makes it very easy to cut and paste and from that produce a tested
solution. If you don't provide that, you are likely to get an untested
solution, because people who answer questions in these newsgroups do this
for fun, and not because they like typing. So you who have the problem
do the boring mechanical work, and we take care of the fun part: the
intellectual challenge.

So, there! :-)

Thanks Ross
This code does work except for the fact it deletes two many records.
Any ideas how to adjust this to make it delete the records I need
deleted? I am not familiar with cursor sql.
Jul 20 '05 #6

P: n/a
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Ross Presser (rp******@imtek.com) writes:
Very clever. I still don't feel comfortable enough with cursors to write
such code off the cuff. :)


Clever? Quite ugly, but it was an ugly problem.
One minor point: you have it computing the difference between
count(tbl1.*) and count(tbl2.*), and deleting that number -- so if tbl1
starts with five and tbl2 with two, it deletes three. But rereading his
original request, I think he wanted two deleted -- and that's how I
wrote my monstrosity.


Do I have to say it again? When you have a problem like this, please
include:
o CREATE TABLE statements for your tables.
o INSERT statements with the sample data.
o The expected result.

This makes it very easy to cut and paste and from that produce a tested
solution. If you don't provide that, you are likely to get an untested
solution, because people who answer questions in these newsgroups do this
for fun, and not because they like typing. So you who have the problem
do the boring mechanical work, and we take care of the fun part: the
intellectual challenge.

So, there! :-)


Thank you Ross and Erland for all your help.
Ross yours worked perfect! I will make sure in the future that I post
my table creation so that it makes more sense.
Jul 20 '05 #7

P: n/a
Philip Mette (ph******@msn.com) writes:
This code does work except for the fact it deletes two many records.
Any ideas how to adjust this to make it delete the records I need
deleted? I am not familiar with cursor sql.


If I have any ideas? Yes, I have ideas. They were in fact in the post
that you quoted, but I repeat it again:
Do I have to say it again? When you have a problem like this, please
include:
o CREATE TABLE statements for your tables.
o INSERT statements with the sample data.
o The expected result.

This makes it very easy to cut and paste and from that produce a tested
solution. If you don't provide that, you are likely to get an untested
solution, because people who answer questions in these newsgroups do this
for fun, and not because they like typing. So you who have the problem
do the boring mechanical work, and we take care of the fun part: the
intellectual challenge.


I am sorry, but if you want correct and tested solution, you need to
put in some effort yourself.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.