DELETE Query is not working...
Hi Guys,
This is a problem I already solved, but I still have no idea why I had the problem in the first place,
so I am asking you to explain what was wrong with my query.
The query deletes records from table1 based on some joins with table2.
The exactly same SELECT works perfectly, but the deletion, gives me an error that I need to specify a table, or that I cannot delete from this table.
Here's the query:
DELETE table1.*
FROM table1 INNER JOIN table2 ON (table1.name=table2.name) AND (table1.address=table2.add
WHERE (((table1.email) Is Not Null))
the Preview shows the correct answers, and if I make it a SELECT instead of DELETE, it shows all the rows correctly too.
But this query cannot be executed...
The way I solved it was:
DELETE *
FROM table1
WHERE ID IN
(
SELECT table1.ID
FROM table1 INNER JOIN table2 ON (table1.name=table2.name) AND (table1.address=table2.add
WHERE (((table1.email) Is Not Null))
);
Now, can anybody PLEASE explain why the hell didn't the first query work?!
This is a problem I already solved, but I still have no idea why I had the problem in the first place,
so I am asking you to explain what was wrong with my query.
The query deletes records from table1 based on some joins with table2.
The exactly same SELECT works perfectly, but the deletion, gives me an error that I need to specify a table, or that I cannot delete from this table.
Here's the query:
DELETE table1.*
FROM table1 INNER JOIN table2 ON (table1.name=table2.name) AND (table1.address=table2.add
WHERE (((table1.email) Is Not Null))
the Preview shows the correct answers, and if I make it a SELECT instead of DELETE, it shows all the rows correctly too.
But this query cannot be executed...
The way I solved it was:
DELETE *
FROM table1
WHERE ID IN
(
SELECT table1.ID
FROM table1 INNER JOIN table2 ON (table1.name=table2.name) AND (table1.address=table2.add
WHERE (((table1.email) Is Not Null))
);
Now, can anybody PLEASE explain why the hell didn't the first query work?!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not really familiar with MySQL but Access works just fine as a front end to MySQL - you could run your queries as pass-through queries assuming MySQL supports that kind of thing - sorry, I've never used it in this way, but I know many people have used it as a back end (I use SQL Server when I have to, personally).
ASKER
Alright, thanks a lot, I will explore and try to figure how it works.
If I won't manage, I know where to come back to ask questions ;)
Thanks again.
If I won't manage, I know where to come back to ask questions ;)
Thanks again.
ASKER
But, this is a bit stupid, IMHO...
"DELETE table1.* FROM table1" should be clear enough for the engine to know what am I trying to delete...
Besides, the documentation of Access wrote that I can use JOIN in _any_ FROM statement...
Couldn't find there anything that will explain that it could break the query... :/
Oh well... it's unfortunate that sometimes I have to use Access for ease of work, because of the visual interface and etc.
Is there a way that I can integrate MS Access interface (application) with some MySQL database perhaps, and MySQL syntax queries?