delete Using Join Statement

[mju]

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]

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));

Try using this on a BACKUP COPY of the database so you can
quickly get back to where you are now if there is a problem.

DELETE tableB.*
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

 

[mju]

thanks Marsh.

I tried it but i got this error

Could not delete from specified tables. (Error 3086)

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));

Try using this on a BACKUP COPY of the database so you can
quickly get back to where you are now if there is a problem.

DELETE tableB.*
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

 

[vanderghast]

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


Which is simply exchanging tableA with tableB, since you want to delete from
tableB records not in tableA.


Vanderghast, Access MVP

 

[Marshall Barton]

I tried it but i got this error

Could not delete from specified tables. (Error 3086)

Tables, plural??

I see that Vanderghast caught my mistake of deleting from
the wrong table. Did fixing that make a difference?