Joins in delete queries

[Guest]

Im not a programer but I have done a lot of work in Access MDB files. One of
the things that I have done in a mdb file that I can not seem to do in and
ADP file is to run Delete queries or update queries that contain joins in
them. Is there anyway to do this? I may be wrong but it seems to me that MDB
files are more user friendly than ADP files.

 

[MH]

It could be that you are linking a "non-updateable recordset" somewhere in
your query, try it with tables.

MH

 

[Guest]

Im dont understand what you mean by that.
below is the qyr that works in a mdb file but I cant seem to do this in ADP

DELETE dbo_tbl_HoldOrderDetails_View.[ORDER#],
dbo_tbl_HoldOrderHeader.CustomerCode, dbo_tbl_HoldOrderDetails_View.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] = dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

would you know what the advantages of an mdb file compared to a adp and vice
a versa.
Thank you

 

[John Vinson]

Im dont understand what you mean by that.
below is the qyr that works in a mdb file but I cant seem to do this in ADP

DELETE dbo_tbl_HoldOrderDetails_View.[ORDER#],
dbo_tbl_HoldOrderHeader.CustomerCode, dbo_tbl_HoldOrderDetails_View.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] = dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

I'm pretty certain that you can only delete from ONE table in a Delete
query, and you're trying to delete from two.

If you have Cascade Deletes set between the Header and Details tables,
you should be able to delete * from dbo_tbl_HoldOrderHeader; the
dbo_tbl_HoldOrderDetails records will be deleted automatically by the
cascade:

DELETE dbo_tbl_HoldOrderHeader.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader
ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] =
dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

If you want to leave the header record alone and delete the detail
records, dbo_tbl_HoldOrderDetails_View will need to be an updateable
view, and you'll need to adjust the query to

DELETE dbo_tbl_HoldOrderDetails_View.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader
ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] =
dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

This will leave the header alone and delete all Detail records for all
customers with CustomerCode equal to "1".

John W. Vinson[MVP]

 

[Michel Walsh]

Hi,


Alternatively, try to delete over a VIEW that makes the join, but delete
from just one table.

You can also use the proprietary syntax (proprietary to MS SQL Server) known
as the DELETE FROM FROM :

DELETE FROM tableName FROM tableName INNER JOIN otherTable ON
tableName.fieldName = otherTable.otherField

or simply an IN clause:

DELETE FROM tableName WHERE tableName.fieldName IN( SELECT otherField
FROM otherTable )


(see http://www.mvps.org/access/queries/qry0022.htm)

Hoping it may help,
Vanderghast, Access MVP

Im dont understand what you mean by that.
below is the qyr that works in a mdb file but I cant seem to do this in
ADP

DELETE dbo_tbl_HoldOrderDetails_View.[ORDER#],
dbo_tbl_HoldOrderHeader.CustomerCode, dbo_tbl_HoldOrderDetails_View.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] = dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

I'm pretty certain that you can only delete from ONE table in a Delete
query, and you're trying to delete from two.

If you have Cascade Deletes set between the Header and Details tables,
you should be able to delete * from dbo_tbl_HoldOrderHeader; the
dbo_tbl_HoldOrderDetails records will be deleted automatically by the
cascade:

DELETE dbo_tbl_HoldOrderHeader.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader
ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] =
dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

If you want to leave the header record alone and delete the detail
records, dbo_tbl_HoldOrderDetails_View will need to be an updateable
view, and you'll need to adjust the query to

DELETE dbo_tbl_HoldOrderDetails_View.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader
ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] =
dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

This will leave the header alone and delete all Detail records for all
customers with CustomerCode equal to "1".

John W. Vinson[MVP]