Solved Delete Records based on another table (1 Viewer)

Number11

Member
Local time
Today, 00:35
Joined
Jan 29, 2020
Messages
607
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, 00:35
Joined
Jul 21, 2014
Messages
2,237
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, 00:35
Joined
Jan 29, 2020
Messages
607
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, 20:35
Joined
Jan 23, 2006
Messages
15,364
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, 00:35
Joined
Jul 21, 2014
Messages
2,237
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

..forever waiting... waiting for jellybean!
Local time
Today, 08:35
Joined
May 7, 2009
Messages
19,169
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));
 

sergey123

New member
Local time
Today, 03:35
Joined
Feb 19, 2021
Messages
3
we have price table and goods table in access we need to make query to delete all price values from table price that absent in table goods and leave all prices in table price that exist in table goods.
this code only update values how do we remake our program (delete valuers from Price.Present that absent in [Goods].Remnant )?**
UPDATE Price INNER JOIN Goods ON [Price].Article = [Goods].Article SET Price.Present = [Goods].Remnant;
 

Users who are viewing this thread

Top Bottom