Delete using join statement

[mju]

hello . i want to run a query using the delete and join statement

I am having issues deleting items from a table.

I have two tables.

Table A and Table B contains all the record. They should both have same
number of records.

Table A fields.
inv_num
inv_date
companyid
order_num
rel_num
trad_partner
report_name
inv_order_num

Table B fields
Counter
Data Type
DATE
TIME
From_name
Receiver name
Receiver ID
order_num
inv_num
ENV_CTRL_
GROUP_CTRL_
Transaction_

I am trying to delete records from table B with no match in table A. where
invoice number and company iD is Null.

Currently, I have 30 records in table A and 40 records in table B.

I want table A and B to have 30 records.
Therefore, I want to delete records in table B that does not exist in table
A.

I tried this but it did not work. Nothing is being deleted. i know that i
have some rcords in table B not in table A. What am I doing wrong. Please
help!!!

DELETE DISTINCTROW [tableA].*, tableB. inv_num
FROM tableA LEFT JOIN tableB ON ([tableA].inv_num= tableB.inv_num) AND
([tableA]. order_num= tableB. order_num) AND
([tableA].companyid=tableB.From_name)
WHERE (((tableB. inv_num) Is Null));

 

[Marshall Barton]

hello . i want to run a query using the delete and join statement

I am having issues deleting items from a table.

I have two tables.

[snip duplicate of another post]

I attempted to answer this question in another newsgroup.

Please do not post the same question multiple times. If you
feel that you must post to more than one forum, cross post
it once by specifying both forums (separated by a comma) in
the To: box

 

[Klatuu]

DELETE * FROM tableA WHERE tableA.inv_num NOT IN (SELECT inv_num FROM tableB;

 

[mju]

Ok. thanks.

I just did it

hello . i want to run a query using the delete and join statement

I am having issues deleting items from a table.

I have two tables.

[snip duplicate of another post]

I attempted to answer this question in another newsgroup.

Please do not post the same question multiple times. If you
feel that you must post to more than one forum, cross post
it once by specifying both forums (separated by a comma) in
the To: box

 

[mju]

Please delete this post.

Thanks.

I dont know what happened. i was repying to the original post. Thans

Ok. thanks.

I just did it

hello . i want to run a query using the delete and join statement

I am having issues deleting items from a table.

I have two tables.

[snip duplicate of another post]

I attempted to answer this question in another newsgroup.

Please do not post the same question multiple times. If you
feel that you must post to more than one forum, cross post
it once by specifying both forums (separated by a comma) in
the To: box