Delete queries from joined tables

[nagyf]

Hi!

I have tables A and B.
1.) How can I delete records from table A only, where A.KEY = B.KEY with a QUERY?
2.) How can I delete records from table A and B, where A.KEY = B.KEY with a QUERY?

I have belived that DELETE queries do not work on joined tables? Somebody has shown me a solution to question
1.) DELETE DISTINCTROW FROM B.KEY,A.* FROM B IINER JOIN A ON B.KEY=A.KEY;

What is the keyword: the DISTINCTROW?
What happens in case of substituting "B.*,A.*"?
What happens if I omit DISTINCTROW?


What are the rules of delete queries containing joins? Where are these exactly written?

Regards
Ferenc Nagy

[tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[ploeryan]

Hello

Try using the SQL

DELETE A.* FROM A INNER JOIN B ON A.[Key] = B.[KEY];

DISTINCTROW (from MS HELP) Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

Hope that helps

 

[nagyf]

TABLE A
KEY NAME
A ANNE
B BRITTANY
C CECILY
D DORIS
E EVE
F FRANCESCA
G GINA

TABLE B
KEY NAME
C CECILY
D DORIS
E EVE

>Try using DELETE A.* FROM A INNER JOIN B ON A.[Key] = B.[KEY];

Indeed:
Remainder of table A
KEY NAME
A ANNE
B BRITTANY
F FRANCESCA
G GINA

Remainder of table B
KEY NAME
C CECILY
D DORIS
E EVE

---------------------------------------------------------
DELETE A.*, B.*
FROM A INNER JOIN B ON A.[Key] = B.[KEY];


Remainder of table A
KEY NAME
A ANNE
B BRITTANY
F FRANCESCA
G GINA

No records remained in table B.
Simple and logic. :-( Why did I believe so long that tricks needed?

Better later than never
Ferenc