473,545 Members | 2,091 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Delete Records From a Table Using Records in Another Table.

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,activi ty_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
7 2976
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,activi ty_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.acc t,X.activity_da te,X.pay_amt,CO UNT(Y.TransID) num
FROM Table1 X
INNER JOIN Table1 Y
ON X.acct=Y.acct
AND X.activity_date =Y.activity_dat e
AND X.pay_amt=Y.PAY _AMT
WHERE X.TransID >= Y.TransID
GROUP BY X.TransID,X.acc t,X.activity_da te,X.pay_amt
) T1
WHERE T1.num <= (
SELECT COUNT(*) FROM Table2 T2
WHERE T1.acct=T2.acct
AND T1.activity_dat e=T2.activity_d ate
AND T1.pay_amt=T2.P AY_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
[posted and mailed, please reply in news]

Philip Mette (ph******@msn.c om) 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,activi ty_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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
On Fri, 20 Aug 2004 21:33:53 +0000 (UTC), Erland Sommarskog wrote:
Philip Mette (ph******@msn.c om) 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,activi ty_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
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
Erland Sommarskog <es****@sommars kog.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
Erland Sommarskog <es****@sommars kog.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
Philip Mette (ph******@msn.c om) 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****@sommarsk og.se

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2221
by: Alex | last post by:
Hi, hi have a table with 2.5 million records which i try do delete. i have several constraints on it too. i tried to delete the records using delete but it does not seem to work. the delete runs forever. hrs... i cannot truncate it as it complains about foreign keys. What is the problem ? Thanks
6
3079
by: Paul T. Rong | last post by:
Dear all, Here is my problem: There is a table "products" in my access database, since some of the products are out of date and stopped manufacture, I would like to delete those PRODUCTS from the table, but I was not allowed to do that, because "there are records related with those PRODUCTS in other tables (e.g. in table "ORDER_DETAIL").
8
2777
by: paulwilliamsonremove | last post by:
Hi, Using Access 2003. I am trying to delete records from one table/query ("qryHistoryPersonIDs") if they don't exist in another table ("qryDonations"). But the SQL syntax I came up with is taking forever or locking up Access 2003. What am I doing wrong?
3
2093
by: John Rivers | last post by:
Hello, I think this will apply to alot of web applications: users want the ability to delete a record in table x this record is related to records in other tables and those to others in other tables etc. in other words we must use cascade delete to do
6
3842
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called "Generations"), and it allows the user to add, edit and delete the various records of the table. "Generations" table has the following fields: "IDPerson",...
5
4131
by: Bob Bridges | last post by:
Start with two tables, parent records in one and child records in the other, a one-to-many relationship. Create a select statement joining the two. Display the query in datasheet mode. When I delete a row, only the child record is deleted from the source tables; the parent record is still there...which is what I wanted. Now display fields...
3
6124
by: igendreau | last post by:
I'm trying to clean up a database of mine, and I need to convert some old DAO code over to ADO. When I was using DAO, I had no problem running this script and deleting the record using rs.Delete. But when I rework the code in ADO, it tells me I can't delete it because there are related records in another table. Never had that problem in DAO...
10
2670
by: nickvans | last post by:
Hello everyone, I'm fairly new to VBA and MS Access (I'm using 2003) but my issue seems like a pretty straight forward one. I would like to delete all records found in one table from another one. I have code which grabs values from a list box (which has as its source "tblNewModPart") then uses them in a Make-Table query which creates a table...
6
4076
by: scott.tang | last post by:
I'm experiencing a very strange problem. My application is MS Access front-end and MS SQL server back-end database. I have a SQL statement that deletes records from a table after an export process. The problem is occasionally when the delete statement is executed, these records no longer display on List Box (not even in the MS Access link...
0
7468
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7401
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7423
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7757
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5329
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3450
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3443
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1884
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
704
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.