Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Update in batch doesn't update the entire records
Message
 
 
À
09/12/2009 17:31:28
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2008
Application:
Web
Divers
Thread ID:
01438298
Message ID:
01438299
Vues:
33
NULLs in M2.AI?

>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.
--sb--
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform