在sql server中使用多个左连接更新记录 [英] update record with multiple left join in sql server

问题描述

先生，



i有查询

  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   < span class =code-keyword> null  和 d.commheirid   不  null  

i得到输出为b.commheirid列值null和d.commHeirID包含值

所以我想更新b.commheirid，无论d.commheirid值是多少都应该在b.commheirid中相同



所以我在查询后写更新

 更新 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 和 
 b.CommHeirID    null  和 d.commheirid   不  null  

但输出会受到0行影响，查询中有什么问题帮助

解决方案

我注意到了一件事。



您更新的表（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值。如果它不是更新表的一行，那么会更新什么？我什么都没想。 :)



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



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



谢谢

< blockquote>我有一个视图

  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

这篇关于在sql server中使用多个左连接更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！