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

Delete using 2 tables

I need to delete records in 1 table based on matching data in a 2nd table.
Below is my syntax. Is this a good syntax for this?
DELETE dbo.InternalCSIAnswers
FROM dbo.InternalCSIQuestions
WHERE (dbo.InternalCSIAnswers.InternalCSIID =
dbo.InternalCSIQuestions.InternalCSIID)
AND (dbo.InternalCSIAnswers.RepairOrderID = 26981
AND (dbo.InternalCSIQuestions.InternalType = 'E')
DavidI prefer the ANSI DELETE syntax:
DELETE FROM InternalCSIAnswers
WHERE EXISTS
(SELECT *
FROM InternalCSIQuestions AS Q
WHERE Q.InternalCSIID = InternalCSIAnswers.internalcsiid
AND Q.internaltype = 'E')
AND repairorderid = 26981 ;
This has the potential advantage of being standard SQL, unlike the
proprietary Microsoft extension you used (although the Microsoft version
often yields more efficient execution).
David Portas
SQL Server MVP
--|||DELETE dbo.InternalCSIAnswers
FROM dbo.InternalCSIAnswers a
join dbo.InternalCSIQuestions q
on q.InternalCSIID = a.InternalCSIID
WHERE a.RepairOrderID = 26981
AND q.InternalType = 'E'
"David" wrote:

> I need to delete records in 1 table based on matching data in a 2nd table.
> Below is my syntax. Is this a good syntax for this?
> DELETE dbo.InternalCSIAnswers
> FROM dbo.InternalCSIQuestions
> WHERE (dbo.InternalCSIAnswers.InternalCSIID =
> dbo.InternalCSIQuestions.InternalCSIID)
> AND (dbo.InternalCSIAnswers.RepairOrderID = 26981
> AND (dbo.InternalCSIQuestions.InternalType = 'E')
> David
>
>

Delete syntax to delete a record from one table if a matching value isn't found in another

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

kfrost:

Maybe something like:

DELETE FROM Broker_Rates
from Broker_Rates a
WHERE not exists
( select 0 from Broker b
where a.BID = B.id
)

Dave

|||

That appeared to do the trick. Curious, what does using the 0 in Select 0 person of your string above accomplish. I was using Select *.

Works, just curious for future reference.

Thanks.

|||

I use the zero because the select list is not relevant. Your syntax will work fine. I need to do something like "0 as dummy" so that it is more obvious that the item is a dummy item.

Dave

|||Cool. Thanks!!

Delete records not matching Top25 in GroupBy

I have a table (tblA) that records the RecordID, UserID and
LastViewedDate (DateTime) of each record opened in tblB where RecordID
is the PK in tblB. I want to construct a query that groups all records
in tblA by RecordID, filters by UserID and keeps only the most recent
25 RecordIDs and deletes the rest.

This gets me a recordset of all RecordIDs filtered by UserID in tblA
but I can't figure out how to sort it by LastViewedDate DESC and to
eliminate those not in the Top25:

SELECT RecordID
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID

Any help is appreciated!
lqYou'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
whererecordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/|||You'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
whererecordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/|||You'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
whererecordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/|||Jason,
Thanks for that solution.
I have to add a small fix to it though for anyone stumbling across this
who might require a similar solution.

delete dbo.tblA
where recordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
AND (UserID = 1234)

Without the "AND (UserID = 1234)" all records regardless of user are
deleted.

Also, users may have to play with the PK of tblA instead of refering to
RecordID which is the PK of tblB, as I had to do to get the results I
wanted:

delete dbo.tblA
where ViewedID NOT in
(
select MaxViewedID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed,
max(ViewedID) as MaxViewedID
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
AND (UserID = 1234)