Update Query in SQL 2005 with inner join

SQL Apprentice Question
I have the following update query

UPDATE Employee
SET Deactivated = 1
FROM Employee AS Employee_1 INNER JOIN
Assignment ON Employee_1.SSN = Assignment.SSN CROSS
JOIN
Employee
WHERE (Assignment.SCHOOLID = '0') AND (Assignment.TERM IS NULL)


and it updates, but the problem is it is updating the complete table and not
filtering with the where statement.


Any ideas?


Celko Answers
Do you really have just one Employee, as you said with your data
element name? Are you really using assembly language bit flags in SQL?
SQL programmers do not set flags; they use predicates and VIEWs to
find the state of their data. Programmers who work with punch cards
set flags.

SQL programmers also know not to use the proprietary UPDATE.. FROM..
syntax. Here is what I think you were trying to do in Standard,
portable, predictable SQL. I also cleaned up your data element names
to look more like ISO-11179:


UPDATE Personnel
SET deactivated_flag = 1
WHERE EXISTS
(SELECT *
FROM Personnel AS P, Assignments AS A
WHERE P.ssn = Personnel.ssn
AND A.ssn = Personnel.ssn
AND A.school_id = '0'
AND A.school_term IS NULL);


One of the MANY reasons that we do not use bit flags or even have
Booleans in SQL is that when someone modifes Assignments.school_term
your deactivated_flag is wrong. Now you need procedural code in a
trigger to fix this, or to run a stored procedure whenever there is any
doubt.


If you use a VIEW and quit thinking like a punch card programmer, then
the data is *always* correct:


CREATE VIEW ActivePersonnel (..)
AS
SELECT ..
FROM Personnel AS P
WHERE WHERE EXISTS
(SELECT *
FROM Assignments AS A
WHERE A.ssn = P.ssn
AND A.school_id = '0'
AND A.school_term IS NULL);