SQL- How to delete records based on QUERY ?

[Martin \(Martin Lee\)]

I have TABLE1 and TABLE2.
TABLE1 has field "DEALER CODE" , "DEALER NAME", "SALARY"
TABLE2 has field "DEALER CODE" , "VOLUME" (doesn't have "DEALER NAME")
The records in TABLE1 and TABLE2 is "one to one", linked by the field
"DEALER CODE"
And, I create QUERY3 to contain "DEALER CODE" , "DEALER NAME", "SALARY"
"VOLUME"


Now, I want to delete all the records where field "DEALER NAME"= "dealer1"
and "dealer2"
When doing this, I face such problem:

I can do this to TABLE1 with VBA DoCmd.RunSQL "DELETE * FROM TABLE1 WHERE
DEALERNAME in ('dealer1','dealer2')"

However, I can't do this to TABLE2 because TABLE2 has only the DEALER CODE
yet not have the DEALER NAME.

So, I have to delete according to QUERY3


MY QUESTION IS:
How to write a SQL VBA to delete base on QUERY3 ? (delete base on QUERY,
not base on TABLE)



Thanks!

Martin

 

[Guest]

Hi Martin,

If you set up your table relationship such that your relationship between
Table1 and Table2 is set to "cascade deletes" then deleting the item from
Table1 will delete from Table2.

As an aside, if your table1 and table2 really do have a "one to one"
relationship as you have stated, the information should be in a single table.

Damian.

 

[Martin \(Martin Lee\)]

Hello Damian,

Thank you for your reply.

Unfortunately, Table1 and Table2 are not "cascade deletes" . More
unfortunately, Table1 and Table two should be in a single table but they
have been seperated by me long long time ago : )

That's why I am facing a headache problem.

Martin

 

[Guest]

Alright, to get around your problem, you would do it in two steps. First,
delete from table2 where the codes match table 1, second delete from table1
as previously described. Like this:

docmd.runsql "delete from table2 where [Dealer Code] in (select [Dealer
Code] from Table1 where [DealerName] in (" & LIST_OF_DEALER_NAMES & "))"

Hope that helps.

Damian.

 

[Martin \(Martin Lee\)]

I want to know, whether SQL VBA could UPDATE data or DELETE data according
to QUERY, rather than TABLEs

For example, something like:
DoCmd.RunSQL "DELETE * FROM QUERY3 WHERE DEALERNAME in
('dealer1','dealer2')"

or something like:

UPDATE QUERY3 INNER JOIN QUERY2 ON QUERY3.dealercode3=QUERY2.dealercode2
SET QUERY3.amount3 = QUERY2.amount2;





Thanks!

 

[John W. Vinson]

I want to know, whether SQL VBA could UPDATE data or DELETE data according
to QUERY, rather than TABLEs

Yes, if the query is updateable.

For example, something like:
DoCmd.RunSQL "DELETE * FROM QUERY3 WHERE DEALERNAME in
('dealer1','dealer2')"

You'll need to specify which *table* you want to delete records from:

DELETE Table2.* FROM QUERY3 WHERE...

or, better, just turn QUERY3 into a DELETE query:

DELETE Table2.*
FROM Table1 INNER JOIN Table2
ON table1.dealercode = table2.dealercode
WHERE table1.dealername IN ("dealer1", "dealer2")

or something like:

UPDATE QUERY3 INNER JOIN QUERY2 ON QUERY3.dealercode3=QUERY2.dealercode2
SET QUERY3.amount3 = QUERY2.amount2;

Without seeing QUERY2 or QUERY3 in SQL view it's hard to be sure. If
possible it's more efficient to simply change a SELECT query to an
Update query, just as above.

John W. Vinson [MVP]