"AMP" <u26259@uwe> wrote in message news:65e5bf1eb1ab2@uwe...
>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?
>

>Why are you joining employee multiple times?  And why a CROSS JOIN, of all
>things?
>
>UPDATE e
>SET Deactivated = 1
>FROM Employee e
>INNER JOIN Assignment a
>ON a.SSN = e.SSN
>WHERE a.SchoolID = '0'
>AND a.Term IS NULL;
>
>>I have the following update query
>>
>[quoted text clipped - 11 lines]
>>
>> Any ideas?

>> That was  what SQL 2005 did to the code when I ran it.
>
>Stop using the designer!  Write your views/queries in a query window in
>Management Studio.
>
>> I ran what you had and got the following two pop up errors
>
>Again, stop using the designer.  It's making a mockery of your code.
>
>A

"AMP" <u26259@uwe> wrote in message news:65e64a2063c4b@uwe...
> Haven't used designer, I just use the query window and SQL in the table
> view
> only.
>
> Aaron Bertrand [SQL Server MVP] wrote:
>>> That was  what SQL 2005 did to the code when I ran it.
>>
>>Stop using the designer!  Write your views/queries in a query window in
>>Management Studio.
>>
>>> I ran what you had and got the following two pop up errors
>>
>>Again, stop using the designer.  It's making a mockery of your code.
>>
>>A
>

>I don't know what "in the table view only" means.
>
>Open Management Studio.
>
>Hit Ctrl+Alt+G to view your registered server.
>
>Right-click your server and choose "Connect > Object Explorer".
>
>Right-click the correct database and hit Ctrl+N.
>
>Type your query.
>
>If Management Studio adds CROSS JOINs here, please let us know.
>
>If you are typing queries anywhere else, stop.
>
>A
>
>> Haven't used designer, I just use the query window and SQL in the table
>> view
>[quoted text clipped - 10 lines]
>>>
>>>A

>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);