Delet query based on values in another table ?

[GBall]

Hi,
this is very simple in Oracle, but I can't see a way to do it in Access.

Basically what I want to do is:
delete from a where a.key = b.key and b.type not in (1,2,3).

Am I missing something ?

Regards,
Graham

 

[RoyVidar]

I think I'd try something like this:

[tt]delete a.*
from a INNER JOIN b ON
a.key = b.key
where
b.type not in (1,2,3)[/tt]

Roy-Vidar

 

[GBall]

Well yes, that's what I thought.
The designer, for some reason, created the first line as
delete a.*, b.type

and when I tried to run it I got the error message
"Cannot delete from the specified tables".

Thinking that it wanted to delete from the lookup table, which is a read-only link, I removed the b.type from the delete, but still get the same message.
I can delete from the target table via a query with no problem, so the difficulty must be with the lookup table.
But what ?

This is the acttual code:
DELETE P60_Payment_Detail.*
FROM P60_Payment_Detail INNER JOIN RO_EXII_V_PRODUCT ON [P60_Payment_Detail].[PolicyNumber]=[RO_EXII_V_PRODUCT].[SOURCE_SYSTEM_PRODUCT_REF]
WHERE ((([RO_EXII_V_PRODUCT].[PRODUCT_TYPE_ID]) Not In (220100,91000085)));



Regards,
Graham

 

[RoyVidar]

Hm - don't know, SQL has never been my strong side, perhaps try something like this:

[tt]DELETE a.*
FROM P60_Payment_Detail a
WHERE a.PolicyNumber =
(select
b.SOURCE_SYSTEM_PRODUCT_REF
from
RO_EXII_V_PRODUCT b
where
b.SOURCE_SYSTEM_PRODUCT_REF = a.PolicyNumber
and b.PRODUCT_TYPE_ID Not In (220100,91000085)
)[/tt]

Roy-Vidar

 

[GBall]

Well, I've just tried something similar.
I created a select query for the records that I wanted to delete, then created a delete query with a straight join from p60_payment_detail to the new query.
Still get the same error.

I pasted your query in and ran it, but after watching the hourglass for quite a while, I gave up.
In Oracle, that would have worked a treat, but I'm not sure that Access is as sql-minded as that.

Regards,
Graham

 

[GBall]

This is crazy.
I've seen examples of this done on the net !

I've now created a table of the items to be deleted, created a delete query with a join to it and I still get the error message.
There must be something very basic wrong here.


Regards,
Graham

 

[PHV]

Is P60_Payment_Detail a linked table too ?
Have you the delete permission granted for this table ?
Does access know the PrimaryKey of this table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GBall]

No, it's not linked.

I think I may have stumbled on the answer though.

I created a new query and typed some sql in from scratch and it worked.
I went to design view and the new Access table with the keys of the records that I wanted to delete from p60_payment_detail didn't show.

It would appear that you only need to have the table you want to delete from showing in the design?

Regards,
Graham

 

[PHV]

And this ?
DELETE FROM P60_Payment_Detail
WHERE PolicyNumber In (SELECT SOURCE_SYSTEM_PRODUCT_REF FROM RO_EXII_V_PRODUCT WHERE Not PRODUCT_TYPE_ID In (220100,91000085))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GBall]

Yes, that was the sql I used.

However, if the linked table is shown in the design pane, it comes up with that error message.

Remove the table and paste that code back in and it works.

I guess that somewhere there is some smallprint that shows how it should be done, but the error message could have been more helpful - MS bless 'em !

Regards,
Graham

 

[PHV]

if the linked table is shown in the design pane
I personally avoid the query grid as i hate the way access mess my SQL code ...