Could not delete from specified tables

Question

Im sure Im doing something so naïve here.  I was an Access MVP at one point but I haven't used access for 14 years or so.  I am doing the simplest of delete queries and Im stuck

Im using query builder but here is the SQL

DELETE Sanjose_check_ins.*
FROM Sanjose_check_ins INNER JOIN SJC_check_ins_to_delete1 ON Sanjose_check_ins.ID = SJC_check_ins_to_delete1.ID;

Answer 1

Hi,

See if this makes any difference:

DELETE DISTINCTROW Sanjose_check_ins.*
FROM ...

Hope it helps...

Answer 2

Try:

DELETE FROM Sanjose_check_ins 
WHERE Sanjose_check_ins.ID in (
   SELECT SJC_check_ins_to_delete1.ID
   FROM  SJC_check_ins_to_delete1);

and check for possible case sensitivity.

Also didn't use it for a while... since Access'95 released.

---

Sincerely, Highly skilled coding monkey.

Answer 3

DELETE *
FROM Sanjose_check_ins
WHERE EXISTS
     (SELECT *
       FROM SJC_check_ins_to_delete1
       WHERE SJC_check_ins_to_delete1.ID = Sanjose_check_ins.ID);

I would recommend that you do not use the generic 'ID' as a column name, however.  Instead name the key so that it clearly identifies it as an attribute of the entity type modelled by the table, e.g.  Sanjose_check_in_ID.  This provides greater semantic clarity in complex SQL statements.

---

Ken Sheridan, Stafford, England

Answer 4

>This provides greater semantic clarity in complex SQL statements.

Cool.

But you mentioned "ENTITY" - you not supposed to see a "complex SQL statements" when you use Entity.

Other way - using Sanjose_check_in_ID in code you create misunderstanding what this field mean - Primary Key or Foreign Key, If PK always is IDs (I didn't use ID as it is reserved in some databases) - there are no misunderstanding as it used always with object.

---

Sincerely, Highly skilled coding monkey.

Answer 5

Did you solve your problem?

Just option - you may have references to Sanjose_check_ins from another table.

In this case you need to have a cascade deletion on or delete relevant rows from related table. 

---

Sincerely, Highly skilled coding monkey.

Answer 6

Other way - using Sanjose_check_in_ID in code you create misunderstanding what this field mean - Primary Key or Foreign Key,

Hi Andrey,

Not necessarily.

In all my (100+) applications I have a consequent naming convention. E.g. with the Entity Persons I define an Item = "Person". Tablename becomes Person_tbl, and PK on the field Person_id.

All other fields ending on "_id" are foreign keys to other Items (Entities). Some id_fields can refer to the same Item, like Father_id and Mother_id in a genealogic application. Both refer to the Item Person.

In this way I can completely automatic generated any "complex SQL statement", based on which Items I want to combine.

Without any misunderstanding.

Imb.

Answer 7

> All fields ending on "_id" are foreign keys

Ok.

> and PK on the field Person_id.

As far as I can read - Person_id is a foreign key. :)

And your code look like:

Person person = new Person()
if ( person.Person_id == MyCatch) { ... }

and my: 

Person person = new Person()
if ( person.IDs == MyCatch ) { ... }

No much difference?

But, as show my experience, I would spend  up to 70% less time on explanation for juniors/middles how to code what they are ordered to code when used IDs instead of Person_id. And all this regards that they not confused by PK/FK definition.

---

Sincerely, Highly skilled coding monkey.

Answer 8

> All fields ending on "_id" are foreign keys

Ok.

> and PK on the field Person_id.

As far as I can read - Person_id is a foreign key. :)

Hi Andrey,

Very good catch!

The line should be read as:

> All OTHER fields ending on "_id" are foreign keys

The "best" way is your consistent housestyle, and that is possible with both approaches. And with a firm housestyle you can build new applications for more then 95% completely automatic.

Imb.

Answer 9

> And with a firm housestyle you can build new applications for more then 95% completely automatic.

I do 100% automated build from database as source.

Even business logic are picked up from custom snippets. :)

But this not depend on naming convention. Naming convention play role when somebody need an explanation of what need to be done or what was done improperly. In this case it's take time to figure out where is PK and where is FK. The convention I use just exclude a needs of PK/FK distinction.

What you didn't take on account - English is just 5-th of my languages and some time I work with people who have very limited knowledge in English and we didn't have another language in common - so, there are a HUGE problem in communication. Any reduction of explanation complexity are very very welcomed. So, it is IDs for PK and "ends with ID" for FK. 

---

Sincerely, Highly skilled coding monkey.

Answer 10

The convention I use just exclude a needs of PK/FK distinction.

Hi Andrey,

I understand your point, I understand my point. Slighty different boundary conditions or demands lead to slightly different conventions. And then we did not even go the kind of key: natural, composed, artificial, …

But I am afraid that is a little outside the scope of this thread.

Imb.

Answer 11

> we did not even go the kind of key: natural, composed, artificial, …

Always artificial. 

But there are no problem to accommodate any other type and keep convention on palace. For composed it will remain IDs for PK and <MasterTable>ID for FK. Just will be added two additional types - containers for multiple fields.

With natural... hmmm... Ok, natural... just add field and a trigger and sequence to fill an artificial PK key... :) Once I have server side JS application with natural key... ooo, good... there even was no option to build a schema - types of the PK/FK fields was not compatible... So, if you insist - no problem - just add an artificial and fill it with trigger,

---

Sincerely, Highly skilled coding monkey.

Answer 12

Always artificial. 

Hi Andrey,

That is also the way I work: always artificial, because of performance, and because of generalization, thus "automate-ability".

Imb.