joined table delete not working

[citychap26]

Hi All,

Getting rather annoyed by a simple delete:

DELETE tblCust.*
FROM tblCust, tblTeam
WHERE tblCust.team = tblTeam.ID
AND tblCust.name="ken" AND tblTeam.subName="b1";

It returns an error (could not delete from specified tables)

Where am I going wrong?

Cheers

SK

 

[PHV]

DELETE tblCust.*
FROM tblCust INNER JOIN tblTeam ON tblCust.team = tblTeam.ID
WHERE tblCust.name="ken" AND tblTeam.subName="b1";

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

 

[citychap26]

Hi PHV,

Thanks but it did not work (

Still got the same error...

 

[lespaul]

Is referential integrity enforced? Are you trying to delete parent records that still have child records?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[citychap26]

Nope it has nothing to do with referential integrity.

Here's what I did in Oracle to delete

DELETE *
FROM TBLCUST
WHERE name in (SELECT a.NAME
FROM TBLCUST a, TBLTEAM b
WHERE a.team = b.ID
AND a.NAME = 'ken'
AND b.subName = 'b1');

I pulled this over to MS Access and it works. However there would be a problem if more than two fields needed to be used. Not sure if I could have to 'in' statments?

Cheers

SK

 

[lespaul]

I think if you need to use two fields you can use the EXISTS statement, but I'm not real clear how it works!! Always new things to learn!!

les