Why does my DELETE query not work?

Many people assume that DELETE queries are just like SELECT queries, in that you want to retrieve or affect a certain group of columns hinging on a condition (e.g. WHERE clause). The syntax I generally see, and which causes the most problems, is as follows: 
 

DELETE * FROM foo WHERE [...]

 
The problem here is that the DELETE command does not take columns as a parameter; you're deleting ROWS, not COLUMNS. You can't delete just one column in rows that match a given set of criteria; you delete the entire row(s). So you can change it to one of the following: 
 

DELETE FROM foo WHERE [...]  DELETE foo WHERE [...]

 
(The latter usually scares people, but the two statements are functionally equivalent.) 
 
If you are not using a WHERE clause, and are trying to empty the entire table, the following incurs less logging and so will be faster (most noticeable on large tables): 
 

TRUNCATE TABLE foo

 
This also resets the IDENTITY column back to its initial seed. One caveat: it is not allowed on tables referenced by a foreign key; you will receive this error: 
 

Server: Msg 4712, Level 16, State 1, Line 1  Cannot truncate table 'foo' because it is being referenced by a FOREIGN KEY constraint.

 
If you forgot to use a WHERE clause, and now need to recover your data, see Article #2449. In the future, you may wish to employ this method when executing ad hoc queries in Query Analyzer: 
 

BEGIN TRAN  -- query here

 
Now, you are free to snoop around after you execute the command, and make sure everything worked correctly. Depending on the result, you can either COMMIT TRAN or ROLLBACK TRAN. 
 
Another point of confusion is the DELETE based on a JOIN. For example, let's say you wanted to delete the rows in foo that also exist in bar. A common mistake is to assume that this will work: 
 

DELETE Foo      INNER JOIN bar      ON foo.PK = bar.PK

 
Yields: 
 

Server: Msg 156, Level 15, State 1, Line 2  Incorrect syntax near the keyword 'INNER'.

 
What you need to do is delete from the table, and perform the join in the subquery, e.g.: 
 

DELETE Foo      FROM Foo      INNER JOIN bar      ON foo.PK = bar.PK

 
However, and while it may not be completely ANSI-friendly, I would prefer to do the following simply for readability: 
 

DELETE Foo      WHERE PK IN       (          SELECT PK FROM bar      )

 
This makes it *much* clearer that your intention is to delete rows *only* from the table foo.