Delete query with multiple tables

[sharpnova]

I have two tables:

table1: id, value1, value 2

table2: id, value3

I want to make a delete query that will do the following:

Delete all records from table 1 that have a matching record in table2 (matched by id) where value3 in table2 is "N"

So I don't want to delete anything from table2. Just from table1.

And only where there exists a record in table2 with the same id and value3 = "N"

I'd like to know how to do this in design view without writing sql
But if it can only be done with SQL, that works as well.

 

[spikepl]

In the query designer, make a normal SELECT query that shows the records you wish to delete. When that works, change the query to a DELETE query

 

[sharpnova]

How do I specify which table I'm deleting from?

Unlike make-query, when I select delete-query, it doesn't popup with a prompt to type a table name.

 

[spikepl]

When you are viewing your SELECT query that shows what you want to delete

1. Select SQL view
2. Replace "SELECT * FROM" by "DELETE * FROM"
3. Save

Backup your data before you test run the query

 

[sharpnova]

When you are viewing your SELECT query that shows what you want to delete

1. Select SQL view
2. Replace "SELECT * FROM" by "DELETE * FROM"
3. Save

Backup your data before you test run the query

This isn't answering my question. I know how to turn it into a DELETE query.

How will I know which table is having records deleted from it?

If there are two tables there, it could be either one.

 

[spikepl]

Then you have not done what I suggested in #1 correctly. Show the SELECT query

 

[sharpnova]

I'm simply asking a question. How will I know which table is having the records deleted from it.

Here is the Select Query:


SELECT table2.value3
FROM table1 INNER JOIN table2 ON table1.id = table2.id
WHERE (((table2.value3)="N"));

There is no "SELECT *" here to turn into a "DELETE *"

 

[spikepl]

Your query does not show the records in table 1 that you wish to delete.

To do that your query must start with
SELECT * FROM Table1 ....

So again: make the query such that it shows the records from the table you wish to delete.

When that works, then you replace SELECT by DELETE and it will delete the specified records from Table1

and your question "from which table" does not arise at all. It deletes from the table specified in the DELETE * FROM this_table ... all the rest is used for selecting which records

 

[sharpnova]

How would I generate the query I'm looking for in design view?

What I tried was showing table1 and table2

linking id in one to the id in the other

then dragging value3 down to the fields section and putting "N" in the criteria


When I ran this, it selected all the records I was looking for.

Should I be generating this query differently?

 

[spikepl]

Try this
SELECT *
FROM table1 INNER JOIN table2 ON table1.id = table2.id
WHERE (((table2.value3)="N"));

 

[sharpnova]

Try this
SELECT *
FROM table1 INNER JOIN table2 ON table1.id = table2.id
WHERE (((table2.value3)="N"));

Yep this works perfectly. And the delete works too.

I was just hoping to be able to generate this query in design view. (But it might not be possible for something this specific)

I suppose I need to learn SQL syntax really well.

Thanks for your help.

 

[spikepl]

WHat do you mean in design view? in the query designer? You just drag the "*" from the first table down on to the grid, and do the rest as before.