Solved Delete Records based on another table

[Number11]

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]

Hi,

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

It should be as simple as something like:

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]

Hi,

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

It should be as simple as something like:

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]

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]

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.

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]

another variation:

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]

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;