Solved Delete Records based on another table (1 Viewer)

Number11

Member
Local time
Today, 03:31
Joined
Jan 29, 2020
Messages
228
Hi,

So i have a table that contains account numbers which i need to delete all records from a master table as i have 350 to delete was looking for a way to look up my list that needs to be deleted and delete only those from master table is that possible?
 

cheekybuddha

AWF VIP
Local time
Today, 03:31
Joined
Jul 21, 2014
Messages
557
Hi,

Please give the table and field names of the tables involved.

It should be as simple as something like:
SQL:
DELETE
  *
FROM MasterTable
WHERE AcctNo IN (
  SELECT AcctNo 
  FROM OtherTable
);

You may need a WHERE clause to limit the AcctNo's from the secondary table.

hth,

d
 

Number11

Member
Local time
Today, 03:31
Joined
Jan 29, 2020
Messages
228
Hi,

Please give the table and field names of the tables involved.

It should be as simple as something like:
SQL:
DELETE
  *
FROM MasterTable
WHERE AcctNo IN (
  SELECT AcctNo
  FROM OtherTable
);

You may need a WHERE clause to limit the AcctNo's from the secondary table.

hth,

d
oh ok sounds good so the field name in list to delete table called Delete is "Account" and the field in the master table called master is "Account No"
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:31
Joined
Jan 23, 2006
Messages
13,481
Further to David(CheekyBuddha)'s comment, here is a link (that used to be a sticky in this forum)
regarding including/excluding records from some process.
 

cheekybuddha

AWF VIP
Local time
Today, 03:31
Joined
Jul 21, 2014
Messages
557
OK,

First make a back-up of your Master table in case you don't get the results you expect!

Then substitute your table and field names.
SQL:
DELETE
  *
FROM Master
WHERE [Account No] IN (
  SELECT 
    Account 
  FROM [Delete]
);
Field/Table names that contain spaces or that are Reserved Words must be enclosed in square brackets.

Run the query and there should be no records in table Master where [Account No] matches any Account in table [Delete]

hth,

d
 

arnelgp

error reading drive A:
Local time
Today, 11:31
Joined
May 7, 2009
Messages
11,183
another variation:

Code:
DELETE Master.*,
Exists (SELECT T1.* FROM [Delete] T1 WHERE T1.Account = Master.[Account No])
FROM Master
WHERE (((Exists (SELECT T1.* FROM [Delete] T1 WHERE T1.Account = Master.[Account No]))<>False));
 

Users who are viewing this thread

Top Bottom