Delete from one table with key linked to another

A

alex_hatzisavas

Registered User.

Local time

Today, 07:19

Joined

May 18, 2006

Messages

12

• Oct 26, 2006

• #1

Hi all.

I kind of got stuck with this one.

I'm trying to use a Delete query to delete some records from Table A.

Let's say Table A has three fields: A1, A2 and A3

Then there's another table, Table B, with two fields: B1 and B2

Within one Delete Query, i need to delete all the records from Table A for which: A1=B1

Thanks for your help.

Alex

 

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 07:19

Joined

Sep 12, 2006

Messages

16,005

• Oct 26, 2006

• #2

join the two tables in your query via the link you want. ie A1, B1

pull the star from table A into the query design

change the query type to a delete type

1) change to spreadsheet view, that will show you what it is going to delete first
2) when you lcick exclamation mark to run it, it will prompt you to confirm delete.

Be careful to check it, as you can delete data incorrectly very very easily.

 

A

alex_hatzisavas

Registered User.

Local time

Today, 07:19

Joined

May 18, 2006

Messages

12

• Oct 26, 2006

• #3

Hmm, afraid not...

You see, trying to use an Inner Join kind of setup will NOT work in this situation, which was exactly my problem.

The solution (thanks Niko) is to use the below nested SQL statement:

DELETE * FROM Table1
WHERE A1 In (SELECT B1 FROM Table2)

 

boblarson

Smeghead

Local time

Yesterday, 23:19

Joined

Jan 12, 2001

Messages

32,059

• Oct 26, 2006

• #4

And I suspect that the reason gemma's suggestion wouldn't work is that one of the tables didn't have a primary key and therefore the query when joined wasn't updateable.

 

A

alex_hatzisavas

Registered User.

Local time

Today, 07:19

Joined

May 18, 2006

Messages

12

• Oct 26, 2006

• #5

Well actually the error message i got was:
'Could not delete from specified tables'.

 

boblarson

Smeghead

Local time

Yesterday, 23:19

Joined

Jan 12, 2001

Messages

32,059

• Oct 26, 2006

• #6

would mean the same - to know if it wasn't an updateable query, which means it can't delete either, you could test it by changing it from a delete query to a select query and then when you open it to view, down on the status bar it would say "This recordset is not updateable" and then you would know that is why it couldn't be run as a delete query either. If it isn't updateable as a select query, it isn't updateable as anything else either.

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 02:19

Joined

Feb 19, 2002

Messages

47,172

• Nov 1, 2006

• #7

The join will work once both tables have primary keys defined. All tables should have primary keys anyway.