SQL Delete question

[salvelinus]

The first SQL code below is in an existing db's queries. What I'm wondering is why [Main].[Hospital] is in there 3 times? Wouldn't once be sufficient? The full table with all hospitals (3 of them) gets imported into a new db, then all data except for 1 get deleted. Wouldn't the second code do the same thing?

DELETE [Main].[Hospital] AS Expr1, [Main].[Hospital] AS Expr2, [Main].[Hospital] AS Expr3
FROM Main
WHERE ((([Main].[Hospital])<>[Which Hospital?]));

DELETE Hospital as Expr1 FROM Main WHERE Hospital <> [Which Hospital?]

[RobDog888]

Not quite. The line WHERE ((([Main].[Hospital])<>[Which
Hospital?])); will prompt the user to enter the hospital name not
to delete three times because there are three alias', I believe,
and the fourth will be deleted since it was not entered. [Which
Hospital?] is an unknown field so Access (I assume) will prompt
you for the value of that field to use.

To optimize this query test...

DELETE Hospital FROM Main WHERE Hospital = [Which Hospital to DELETE?]

This is reverse logic. Instead of
asking which tables not to delete, try deleting only one which the
user prompt will provide. Then import the remaining three tables
into the new database.

Try this out on a copy or test database and verify if it produces
the desired effect before using any code modifications on a live
production database.

HTH.

[techgnome]

First, I've _never_ gotten a delete to work with fields listed.... but then Access is weird....

DELETE FROM Main WHERE Hospital = [Hospital to delete?]

Should be sufficient.

TG

[RobDog888]

Oops, I included Hospital in the DELETE list by mistake.
TG is right, should be left empty or Main.

[salvelinus]

Originally posted by RobDog888
Not quite. The line WHERE ((([Main].[Hospital])<>[Which
Hospital?])); will prompt the user to enter the hospital name not
to delete three times because there are three alias', I believe,
and the fourth will be deleted since it was not entered. [Which
Hospital?] is an unknown field so Access (I assume) will prompt
you for the value of that field to use.

Yes, Access currently prompts for the parameter, but only does so once. I want to put this in code instead of using a built in query, using an input box to get the hospital to keep. Something like

strHospital = InputBox("Enter Hospital")
strSQL = "DELETE Hospital FROM Main WHERE Hospital <> '" & strHospital & "'"

Now that I think about it, maybe it shoud be

DELETE * FROM Main WHERE Hospital <> strHospital

since I don't want any of the data from the other two hopitals in the copied db.