SQL .

SQL Delete

SQL Delete Example One

Q: How would we remove all John Waters' films from the director/film table?
   
 Delete From Director_Film_Table
  Where Director_Last_Name  = "Waters" 
    and Director_First_Name = "John";
Note: This would remove all films where the director is John Waters.
Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.
Comment by TheMadProfessor on 2010-07-08 Rate this Comment

No, the SQL executes as written, with or without parenthesis. Since the WHERE clause is written with an AND, both sets of criteria must be true for the deletion to occur. If it had been written with an OR instead, then it would execute as you suggest - all directors with a first name of "John" OR a last name of "Waters" would satisfy the criteria.

Comment by archive on 2008-08-04 Rate this Comment

I'm just starting our in SQL and have picked out what I logically see as a paradox using one of your very helpful examples.

thus:
Delete Example #1
Q: How would we remove all John Waters' films from the director/film table?
SQL: Delete From Director_Film_Table
Where Director_Last_Name = "Waters"
and Director_First_Name = "John";

Note: This would remove all films where the director is John Waters.


My question is this:
Should the query not read:

Delete From Director_Film_Table
Where (Director_Last_Name = "Waters" and Director_First_Name = "John");

shouldn't there be a set of brackets in place?
Wouldn't your query delete all records that have a Last Name as "Waters" and a First Name as "John" irrespective of whether the Director's name was "John Waters" or "John Someone" or "Someone Waters". My reasoning is that a record is deleted wherever a "Waters" appears, and likewise wherever a "John" appears. What would happen if we had more than one John as a First Name and more than one Waters as a Last Name, like "John Jones", "John Smith" and "Mike Waters, "Steve Waters". Are the brackets then necessary to counter this confusion, or does SQL only look for cases where "John" and "Waters" appear in the same record, thereby making brackets unnecessary

 
Sign in to comment on SQL Delete.