update record with multiple left join in sql server

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

hi sir,

i have query

SQL

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

SQL

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

Posted 12-Aug-15 23:32pm

invisible@123

Updated 12-Aug-15 23:42pm

Suvendu Shekhar Giri

v2

Add a Solution

Comments

Suvendu Shekhar Giri 13-Aug-15 5:48am

Everything looks fine. Make sure that you have selected the same database for both the queries.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Advay Pandya · Answer 1 · 2015-08-13T00:41:00

Solution 1

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.

SQL

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

Posted 13-Aug-15 0:41am

Advay Pandya

Comments

invisible@123 13-Aug-15 7:26am

yes result of Inner join is 0 row affected

invisible@123 13-Aug-15 7:27am

then what to do pls help

invisible@123 13-Aug-15 7:28am

i want b.commheirID should contain d.commheirID value

Advay Pandya 13-Aug-15 9:46am

Yes, if there is no row then what will get updated ? Please share your issue in detail so I can suggest some alternate way....

invisible@123 14-Aug-15 6:05am

sir if i left join i get data,so want to update

Advay Pandya 17-Aug-15 1:22am

You seems still confuse. You are getting data from left join because the columns are there physically from the left table. So, you can update those records from left the table. But there is no data from right side table because of which you are getting NULL columns. So, you cant update those records. Please explain which kind of data you want to update, so we can find alternate solution.

Advay Pandya 17-Aug-15 2:19am

Do u want to update records those are there in "UMCommWithHeldLog" table, but not there in "UMTranImpoPendLog " table ? Something like this ?

invisible@123 17-Aug-15 3:13am

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

invisible@123 · Answer 2 · 2015-08-16T21:13:00

Solution 2

i have one view

SQL

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

SQL

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

Posted 16-Aug-15 21:13pm

invisible@123

Comments

Advay Pandya 17-Aug-15 3:32am

Yes, I got it. if "CommHeirID " is NULL in LEFT join and it is eliminated in the INNER join then it means row does not exixts in the table "UMTranImpoPendLog". So if the row does not exists then you should insert the new row instead of updating it. It returns NULL result because LEFT JOIN will bring you all the rows of left table, no matter row is exists or not in the right table. Please let me know if you have any concern or query on this.

invisible@123 17-Aug-15 3:54am

thanks

Advay Pandya 17-Aug-15 4:02am

Welcome :) . Please ask me if you need any other help in this query.