Deleting matching records

I need your help in MS Access…
There are 2 tables Table A with 50 records and Table B with 5 records
(similar records), I want to delete the 5 records of table B from Table A so
that in the end Table A should have 45 records (assuming all 5 records of
Table B are in Table A)…
Please help me…Mir Khan wrote:
> I need your help in MS Access...
... but this is a SQL Server group!

> There are 2 tables Table A with 50 records and Table B with 5 records
> (similar records), I want to delete the 5 records of table B from Table A
so
> that in the end Table A should have 45 records (assuming all 5 records of
> Table B are in Table A)...
> Please help me...
In SQL Server:
DELETE FROM A
WHERE EXISTS
(SELECT *
FROM B
WHERE B.col1 = A.col
AND B.col2 = A.col2
AND ... etc ) ;
David Portas
SQL Server MVP
--sql

Deleting all rows from table with RI constraints

Hi
does sql server v2000 allow you to specify a delete with
cascade option similar to Oracle (oops) when removing all
rows from a given table or do you have to either drop the
constraints or remove data from the child tables prior to
the delete on the parent table? I do not want to use the
truncate command if possible.
Cheers
GCH| does sql server v2000 allow you to specify a delete with
| cascade option
--
Yes. In the Create Relationship dialog box, there is a checkbox labeled
"cascade delete related fields." This option "instructs the database to
delete corresponding rows from the foreign key table whenever rows from the
primary key table are deleted.
My reference: SQL Server Books Online
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Hello, GCH.
MS SQL Server 2000 suports ON DELETE CASCADE option.
Here is the example:
CREATE TABLE NewOrderDetails
(
OrderID int NOT NULL
CONSTRAINT FK1_NewOrderDetails REFERENCES Orders
(OrderID)
ON DELETE CASCADE
)
I hope this could help you.
"GCH" <anonymous@.discussions.microsoft.com> wrote in message
news:007f01c3bed2$4b982e60$a001280a@.phx.gbl...
> Hi
> does sql server v2000 allow you to specify a delete with
> cascade option similar to Oracle (oops) when removing all
> rows from a given table or do you have to either drop the
> constraints or remove data from the child tables prior to
> the delete on the parent table? I do not want to use the
> truncate command if possible.
> Cheers
> GCH

Deleteing specific rows from a table which have similar values

I have the following table and I'd like to delete rows which have the same
values in the fields MergeFromURN and MergeToURN
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
3 100 300
4 700 800
5 700 800
After my query I'd like my table to look like the following: -
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
4 700 800
I've no idea how to carry out this delete query as whenever I try I also
seem to delete both rows which are the same. Can someone help me to do this.
Thanks for any help anyone can give me.Hi Stephan,
this should work. Just replace 'myTable' with your table name.
Depending on the amount of data, you might want to create some indexes on
the temp-table.
Micha.
SELECT tab1.RecNo, tab1.MergeFromURN, tab1.MergeToURN
INTO #temp
FROM myTable tab1
JOIN myTable tab2 ON (tab1.RecNo <> tab2.RecNo AND tab1.MergeFromURN =
tab2.MergeFromUrn AND tab1.MergeToUrn = tab2.MergeToUrn)
DELETE
FROM myTable
WHERE RecNo IN (SELECT RecNo
FROM #temp)
AND RecNo NOT IN (SELECT MIN(RecNo)
FROM #temp
GROUP BY MergeFromURN, MergeToURN)
DROP TABLE #temp
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:76146231-179D-497C-ADA3-AE707F279984@.microsoft.com...
>I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do
> this.
> Thanks for any help anyone can give me.|||Hi Stephen,
May this statement solve your Problem
CREATE TABLE TABLENAME(RecNo INT, MergeFromURN INT ,MergeToURN
INT)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 1,500, 600)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 2,100, 300)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 3,100, 300 )
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 4,700, 800)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 5,700, 800)
SELECT * FROM TABLENAME
DELETE FROM TABLENAME WHERE RECNO NOT IN (SELECT MIN(RECNO) FROM
TABLENAME GROUP BY MergeFromURN,MergeToURN)
SELECT * FROM TABLENAME
DROP TABLE TABLENAME
If this statement does solve your purpose let me know.
Please post DDL,DML statements so that others can test their queries.
With warm regards
Jatinder Singh
Stephen wrote:
> I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do thi
s.
> Thanks for any help anyone can give me.