I´m trying to run the following SQL code on access 2010, but it returns syntax error in query expression. Any ideas what could be causing it?
Thanks in advance.
DELETE FROM Trn_done
WHERE (Trn_done.training =
SELECT Training
FROM Trainings
WHERE (Trainings.Area = '" & Area & "'))
The bracket should be before the nested SQL as mentioned below
DELETE FROM Trn_done
WHERE Trn_done.training IN (
SELECT Training
FROM Trainings
WHERE (Trainings.Area = '" & Area & "')
)
IN signifying inexperience and misconception. One should use JOIN or EXISTS instead of IN, in every situation where possible. I have seen a 4-level deep nested IN query with ORs and ANDs that was extremely difficult to understand. It took me 20 minutes to rewrite it as a simple query using INNER JOIN, which could then be understood in a few seconds. So don't be promulgating the use of IN as "working just fine". Anyone can write code a computer can understand. But we should write code for humans to understand.IN(..) is necessarily bad or slower than EXISTS or JOIN, and that just is not generally true. Generally, in cases where they are logically the same, modern SQL optimizers will produce the same query plan. And the claim that IN is easier for "humans" to understand is an opinion that has no basis in fact: beginners prefer IN because it's easier to understand. Please stop promoting these myths as facts.INs argument does not apply to the answer that we are commenting on, making it both a strawman and a red herring, you literally have no reason for your position that IN shouldn't be used other than that you think that "serious professionals just use JOINs"? Do you have anything at all to support that claim because it has no basis in my experience. The serious SQL professionals that I know and work with decide whether to use JOIN, IN, EXISTS, etc. on a case-by-case basis, there's no blanket rule to never use IN or that JOIN is always better.IN behavior, and so often IN marks poor queries. I still believe that encouraging the use of IN subqueries--even one deep--is a disservice to beginners. If one way to write a query lends itself to easy modification and another to needless complexity, it seems an easy choice! It's a maintenance, clarity, and consistency problem.You shouldn't EQUAL a select statement, lest multiple rows in your select cause your code to break. Try this instead:
DELETE td
from Trn_done td
inner join Trainings t
on td.training = t.training
WHERE t.Area = '" & Area & "'
SELECT statement if you're sure that this SELECT returns exactly one row.
SELECTreturns a unique row?Areain there... it's not best practice the way you're doing it.