Michel,

Why you're using OR conitions for M2.NoDetailType instead of much simpler to read IN condition?


>I have a weird situation. I have a select like this:
>
>

>DECLARE @Active Bit
>DECLARE @PayAccept Bit
>DECLARE @AddDate DateTime
>DECLARE @Loss_Code Varchar(1)
>DECLARE @NoDetailType Integer
>DECLARE @NoDetailType2 Integer
>DECLARE @NoDetailType3 Integer
>DECLARE @NoDetailType4 Integer
>DECLARE @NoDetailType5 Integer
>
>SET @Active=1
>SET @PayAccept=0
>SET @AddDate='2009-12-09 15:59:51'
>SET @Loss_Code=' '
>SET @NoDetailType=5
>SET @NoDetailType2=6
>SET @NoDetailType3=10
>SET @NoDetailType4=12
>SET @NoDetailType5=8
>
> SELECT M2.AI
> FROM M AS M2
> INNER JOIN B ON M2.NoBilling=B.Numero
> INNER JOIN (
>   SELECT E.NoEntity,E.NoProvince
>   FROM E
>   WHERE E.Active=@Active AND E.PayAccept=@PayAccept
>  ) EResult ON EResult.NoEntity=M2.NoEntity
> WHERE M2.AddDate<@AddDate AND
>  B.NoProvince=EResult.NoProvince AND
>  M2.Loss_Code=@Loss_Code AND
> (M2.NoDetailType=@NoDetailType OR M2.NoDetailType=@NoDetailType2 OR
>  M2.NoDetailType=@NoDetailType3 OR M2.NoDetailType=@NoDetailType4 OR
>  M2.NoDetailType=@NoDetailType5)
>

>
>The table names have been adjusted to only keep the first letter to preserve the confidentiality of table names.
>
>This select returns 27456 records.
>
>Now, the goal to do this select is to update a field in the master table for those that are part of the result, in this case, 27456 records. So, what I did was the encapsulate that command into an update command such as this:
>
>

>DECLARE @Active Bit
>DECLARE @PayAccept Bit
>DECLARE @AddDate DateTime
>DECLARE @Loss_Code Varchar(1)
>DECLARE @NoDetailType Integer
>DECLARE @NoDetailType2 Integer
>DECLARE @NoDetailType3 Integer
>DECLARE @NoDetailType4 Integer
>DECLARE @NoDetailType5 Integer
>
>SET @Active=1
>SET @PayAccept=0
>SET @AddDate='2009-12-09 15:59:51'
>SET @Loss_Code=' '
>SET @NoDetailType=5
>SET @NoDetailType2=6
>SET @NoDetailType3=10
>SET @NoDetailType4=12
>SET @NoDetailType5=8
>
>UPDATE M
> SET Loss_Code='1'
> FROM M
> INNER JOIN (
> SELECT M2.AI
> FROM M AS M2
> INNER JOIN B ON M2.NoBilling=B.Numero
> INNER JOIN (
>   SELECT E.NoEntity,E.NoProvince
>   FROM E
>   WHERE E.Active=@Active AND E.PayAccept=@PayAccept
>  ) EResult ON EResult.NoEntity=M2.NoEntity
> WHERE M2.AddDate<@AddDate AND
>  B.NoProvince=EResult.NoProvince AND
>  M2.Loss_Code=@Loss_Code AND
> (M2.NoDetailType=@NoDetailType OR M2.NoDetailType=@NoDetailType2 OR
>  M2.NoDetailType=@NoDetailType3 OR M2.NoDetailType=@NoDetailType4 OR
>  M2.NoDetailType=@NoDetailType5)
> ) MResult ON MResult.AI=M.AI
>

>
>So, basically, the update part contains a relationship into its own table by the use of the AI field. As surprisinly as it gets, it only updates 26401 records. So, there is something I am missing here. I just don't understand why the update doesn't update all of the 27456 records.