我注意到了一件事。



您更新的表（UMTranImpoPendLog）与L连接EFT OUTER JOIN。



您可以通过将LEFT OUTER JOIN替换为INNER JOIN一次来检查选择的结果吗？



我相信结果将是空白的。

  SELECT  B.CommHeirID，D.CommHeirID 
 FROM  DBAMFDISTData.DBO.UMCommWithHeldLog A 
 INNER   JOIN  DBAMFDISTData.DBO.UMTranImpoPendLog B  ON  A.TranNumb2 = B.TranNumb2 
 LEFT   OUTER   JOIN  BAMASTER C  ON  A.SubBroker = C.CustID 
 LEFT   OUTER   JOIN  vwEffectiveCommUserMastIDs D  ON  C.BAID = D.BAID 
 WHERE  ISProcessed = 0 
和 b.CommHeirID    null  和 d.commheirid   不  null  

请检查以上行数查询。



如果结果为0行受影响，则更新后的结果是正确的。



在更新查询中，只会更新那些将从select语句返回的行。



如果你获得在LEFT JOIN中更新的表的所有行NULL，那么这不是行。它只是一个NULL值。如果它不是更新表的一行，那么会更新什么？我什么都没想。 :)



我对我的数据库表做了同样的尝试并获得了相同的结果。



如果您对此有任何疑虑或疑问，或者我无法正确解释您，请告诉我。



谢谢

我有一个视图

  ALTER   VIEW  [dbo]。[vwEffectiveCommUserMastIDs ] 

 AS  

 SELECT  MAX（CommHeirID ） AS  CommHeirID，BAID，MAX（WEFDate） AS  WEFDate 

 FROM  CommHeirUserMast 

 WHERE  WEFDate< = GetDate（）

 GROUP   BY  BAID 

 GO  

从此我得到commheirid

  SELECT  B.CommHeirID，D.CommHeirID 
 FROM  DBAMFDISTData.DBO.UMCommWithHeldLog A 
 LEFT   OUTER   JOIN  DBAMFDISTData.DBO.UMTranImpoPendLog B  ON  A.TranNumb2 = B. TranNumb2 
 LEFT   OUTER   JOIN  BAMASTER C  ON  A.SubBroker = C.CustID 
 LEFT   OUTER   JOIN  vwEffectiveCommUserMastIDs D  ON  C.BAID = D.BAID 
 WHERE  ISProcessed = 0 
和 b.CommHeirID 是  null  和 d.commheirid   不  null  

hi sir,

i have query

SELECT  B.CommHeirID,D.CommHeirID
FROM DBAMFDISTData.DBO.UMCommWithHeldLog A
LEFT OUTER JOIN DBAMFDISTData.DBO.UMTranImpoPendLog B ON A.TranNumb2 = B.TranNumb2
LEFT OUTER JOIN BAMASTER C ON A.SubBroker = C.CustID
LEFT OUTER JOIN vwEffectiveCommUserMastIDs D ON C.BAID = D.BAID
WHERE ISProcessed=0
and b.CommHeirID is null and d.commheirid is not null

i get output as b.commheirid column value null and d.commHeirID contain value
so i want to update b.commheirid,whatever d.commheirid value is there should be same in b.commheirid

so i write update following query

UPDATE B
  SET B.CommHeirID = D.CommHeirID
  FROM DBAMFDISTData.DBO.UMCommWithHeldLog A
    LEFT OUTER JOIN DBAMFDISTData.DBO.UMTranImpoPendLog B ON A.TranNumb2 = B.TranNumb2
    LEFT OUTER JOIN BAMASTER C ON A.SubBroker = C.CustID
    LEFT OUTER JOIN vwEffectiveCommUserMastIDs D ON C.BAID = D.BAID
  WHERE
  ISProcessed =0 and
   b.CommHeirID is null and d.commheirid is not null

but output will be 0 row affected,what is wrong in query pls help

Hi,

I noticed one thing here.

Your table which is updated (UMTranImpoPendLog) is joined with "LEFT OUTER JOIN".

Can you please check the select result by replacing LEFT OUTER JOIN to INNER JOIN once ?

I am sure the result will be blank.

SELECT  B.CommHeirID,D.CommHeirID
FROM DBAMFDISTData.DBO.UMCommWithHeldLog A
INNER JOIN DBAMFDISTData.DBO.UMTranImpoPendLog B ON A.TranNumb2 = B.TranNumb2
LEFT OUTER JOIN BAMASTER C ON A.SubBroker = C.CustID
LEFT OUTER JOIN vwEffectiveCommUserMastIDs D ON C.BAID = D.BAID
WHERE ISProcessed=0
and b.CommHeirID is null and d.commheirid is not null

Please check row count of the above query.

If the result is "0 rows affected" then the updated result is correct.

In update query only those rows will get updated which will returned from the select statement.

If you get all the rows NULL of the table which is updated in LEFT JOIN then that is not the row. It's just a NULL value. If it's not a row of the updated table then what will get updated ? I guess nothing. :)

I have tried the same with tables of my database and got the same result.

Please let me know if you have any concern or query on this or if I am not able to explain you properly.

Thanks

i have one view

ALTER  VIEW [dbo].[vwEffectiveCommUserMastIDs]

AS

        SELECT MAX(CommHeirID) AS CommHeirID, BAID, MAX(WEFDate) AS WEFDate

        FROM CommHeirUserMast

        WHERE WEFDate <= GetDate()

        GROUP BY BAID

GO

from this i get commheirid

SELECT  B.CommHeirID,D.CommHeirID
FROM DBAMFDISTData.DBO.UMCommWithHeldLog A
LEFT OUTER JOIN DBAMFDISTData.DBO.UMTranImpoPendLog B ON A.TranNumb2 = B.TranNumb2
LEFT OUTER JOIN BAMASTER C ON A.SubBroker = C.CustID
LEFT OUTER JOIN vwEffectiveCommUserMastIDs D ON C.BAID = D.BAID
WHERE ISProcessed=0
and b.CommHeirID is null and d.commheirid is not null