I am trying to perform a delete query using two tables however I only want the data from one table deleteing. The query is based on the "find unmatched query". If data exists in one table and does not match data in its linked table then i want to delete it. However when I run the delete query it asks me to specify the table where I would like to delete the data from because there are two tables in my query. Can anybody help ?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Delete Query 1
- Thread starter maeling
- Start date
-
1
- #2
Can you post your erroneous query here?
It's easier if you see something to work with. ;-)
MakeItSo
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
It's easier if you see something to work with. ;-)
MakeItSo
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
Guess you're Daniel? ;-)
Got your mail with the db, but couldn't open it - unrecognized db format; repairing, converting to acc 2k wouldn't work - but anyway:
here's the sql you need (gotta adapt the table names and fields though):
DELETE sales.*
FROM cust RIGHT JOIN sales ON cust.custID = sales.custid
WHERE (((cust.cust)="" Or (cust.cust) Is Null));
The right join will give all records from sales and only those from cust which contain a valid Cust-ID. This way you can detect invalid sales-entries by checking which fields from cust are empty... ;-)
Good Luck - and backup your db before trying...
MakeItSo
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
Got your mail with the db, but couldn't open it - unrecognized db format; repairing, converting to acc 2k wouldn't work - but anyway:
here's the sql you need (gotta adapt the table names and fields though):
DELETE sales.*
FROM cust RIGHT JOIN sales ON cust.custID = sales.custid
WHERE (((cust.cust)="" Or (cust.cust) Is Null));
The right join will give all records from sales and only those from cust which contain a valid Cust-ID. This way you can detect invalid sales-entries by checking which fields from cust are empty... ;-)
Good Luck - and backup your db before trying...
MakeItSo
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
- Thread starter
- #4
The database I sent was 2K ?
You'll have to forgive me as I am new to SQL.
Do I need to create a new query and if so what type ?
My tables are are called
customerdetailmaintab
salesdetailtab
The commom field name is account number
Please could you resend the SQL to accomodate my DB names it would be most helpful.
Thanks
You'll have to forgive me as I am new to SQL.
Do I need to create a new query and if so what type ?
My tables are are called
customerdetailmaintab
salesdetailtab
The commom field name is account number
Please could you resend the SQL to accomodate my DB names it would be most helpful.
Thanks
You need to create a DELETE query first (empty)
The paste the following SQL code into the query's SQL view.
You need to change the marked "customer" field in the WHERE statement to the respective field of your customerdetailmaintab (not the account number!)
DELETE salesdetailtab.*
FROM customerdetailmaintab RIGHT JOIN salesdetailtab ON customerdetailmaintab.[account number] = salesdetailtab.[account number]
WHERE (((customerdetailmaintab.[customer)="" Or (customerdetailmaintab.[customer) Is Null));
Save the query for future use e.g. as "Del_Old_Sales" or so.
BTW: I know, that your db was not access, that's why I couldn't open it. ;-) What kind of db program are you working with?
![[lightsaber]](/data/assets/smilies/lightsaber.gif "[lightsaber] [lightsaber]")
Regards,
Andy
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
The paste the following SQL code into the query's SQL view.
You need to change the marked "customer" field in the WHERE statement to the respective field of your customerdetailmaintab (not the account number!)
DELETE salesdetailtab.*
FROM customerdetailmaintab RIGHT JOIN salesdetailtab ON customerdetailmaintab.[account number] = salesdetailtab.[account number]
WHERE (((customerdetailmaintab.[customer)="" Or (customerdetailmaintab.[customer) Is Null));
Save the query for future use e.g. as "Del_Old_Sales" or so.
BTW: I know, that your db was not access, that's why I couldn't open it. ;-) What kind of db program are you working with?
Regards,
Andy
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
- Thread starter
- #6
My DB is Access 2000.
Their seems to be an issue with the syntax I think their is a missing bracket somewhere but I'm not sure where.
I didn't understand your coment "You need to change the marked "customer" field in the WHERE statement to the respective field of your customerdetailmaintab (not the account number!)" can you elaborate ?
Their seems to be an issue with the syntax I think their is a missing bracket somewhere but I'm not sure where.
I didn't understand your coment "You need to change the marked "customer" field in the WHERE statement to the respective field of your customerdetailmaintab (not the account number!)" can you elaborate ?
- Thread starter
- #7
Your customerdetailmaintab certainly consist of more than the "account number" field (Why should it else be named "..detail.."
;-).
One of these fields is, as I guess, the Customer Name or the company name.
Use this field name instead of customer in
WHERE (((customerdetailmaintab.customer)="" Or (customerdetailmaintab.customer) Is Null));
OK?
BTW: Perhaps you have exported any form containing code into the db you sent? That would corrupt the database.
You could also have another try by just exporting your tables into a new access 2k db and mail it. Don't worry, I am not interested in contents ;-)
MakeItSo
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
;-).One of these fields is, as I guess, the Customer Name or the company name.
Use this field name instead of customer in
WHERE (((customerdetailmaintab.customer)="" Or (customerdetailmaintab.customer) Is Null));
OK?
BTW: Perhaps you have exported any form containing code into the db you sent? That would corrupt the database.
You could also have another try by just exporting your tables into a new access 2k db and mail it. Don't worry, I am not interested in contents ;-)
MakeItSo
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
Thanx a lot!
![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
- Status
Similar threads
- Question
