在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 viewALTER 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屋!