Looping in MS Access to Delete Rows

Question

I want to delete all rows in a table that include a certain code in the column that contains the codes. EX:

Name     Code
John     3581
Alex     3132
Jake     2123

In another table, I have codes that correspond to a certain keyword. Some of the names have the code which corresponds to my keyword of choice that I want to eliminate. It look something like this EX:

Code    Keyword
3132    apple
2123    apple
4921    banana

Let's say I want to only screen out apple from the table of names. How would I do that?

I tried setting up a loop, but I guess you cant do that in MS Access. Also, I wanted to do try a WHERE statement.

This is what I had in mind

DELETE table1 where table1.numbers = table2.numbers;

I am simply not sure how to execute this code.

*I want to only screen out apple from the table of names* does this mean delete Alex and Jake rows? — forpas, Jun 24 '19 at 18:27
table1 and table2 would need a join, and the where clause would be on the condition you want to delete rows on. See [How to Delete using INNER JOIN with SQL Server?](https://stackoverflow.com/q/16481379/719186) — LarsTech, Jun 24 '19 at 18:29

score 0 · Accepted Answer · answered Jun 24 '19 at 18:33

0

If you want to delete from table1 the rows with Code that in table2 have the Keyword apple, you can do it with EXISTS:

DELETE FROM table1 t1
WHERE EXISTS (
  SELECT 1 FROM table2 t2
  WHERE t1.Code = t2.Code AND t2.Keyword = 'apple'
)

answered Jun 24 '19 at 18:33

forpas

160,666
10
38
76

Thanks for the response! What do t1 and t2 indicate and why is it included? Also why do you SELECT "1". – mib Jun 25 '19 at 13:59

score 0 · Answer 2 · answered Jun 24 '19 at 20:01

0

You can also use in to avoid the correlated subquery:

delete from table1 t1 where t1.code in 
(select t2.code from table2 t2 where t2.keyword = 'apple')

Here, table1 is the table containing Name & Code and table2 is the table containing Code & Keyword - change these table names to suit your data.

answered Jun 24 '19 at 20:01

Lee Mac

15,615
6
32
80

When you do table1 t1 are you instantiating the object kinda like in Java? What is the point of that? – mib Jun 25 '19 at 14:20
To save me some typing ;-) `t1` and `t2` are merely shorthand aliases for the table names, so that the table name only appears in once place in the code (making it easier for you to modify). Sometimes aliases are required so that a table may be referenced more than once in a single query. – Lee Mac Jun 25 '19 at 18:53

Looping in MS Access to Delete Rows

2 Answers2