CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
stackdisplay
stackdisplay

Reputation: 2045

SQL update based on value from other table

As shown in the figure, I have four tables. Only TableB and TableBChild have a foreign key relationship.

What I want to achieve here is to determine whether TableBChild.MemberID matches TableA.memberNo where TableB.isgood is true

If not batch update isvalid to false where condition is not satisfy.

But I am unable to compare TableBChild.MemberID to Member.MemberNo.

Update TableB
Set IsValid = 0
From TableA as A
INNER JOIN TableB as B ON A.TableBNo = B.TableBNo And IsGood = 1
INNER JOIN TableBChild as BC ON B.TableBID = BC.TableBID
                             AND BC.MEMBERID != SELECT MemberID 
                                                FROM Member 
                                                WHERE MemberNo = A.MemberNo // This line is not working

enter image description here

Upvotes: 1

Views: 53

Answers (3)

mtbnunu
mtbnunu

Reputation: 300

What I would do:

UPDATE TableB
SET IsValid = 0
WHERE TableBID NOT IN 
(
-- this will select TableBID's satisfying:
-- TableB.IsGood is true, 
-- and corresponding TableBChild has memberID that exists in TableA.MemberNo
SELECT tbc.TableBID
FROM TableBChild tbc
INNER JOIN TableB tb ON tb.TableBID = tbc.TableBID
INNER JOIN TableA ta ON tbc.MemberID = ta.MemberNo
WHERE tb.IsGood = 1
)

Upvotes: 0

Mike Clark
Mike Clark

Reputation: 1870

Change your last line :

AND BC.MEMBERID != (SELECT MemberID from Member Where MemberNo = A.MemberNo)

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

Try to use NOT IN instead of != like this:

Update TableB
Set IsValid = 0
From TableA as A INNER JOIN TableB as B
                      ON A.TableBNo = B.TableBNo And IsGood = 1
                 INNER JOIN TableBChild as BC
                       ON B.TableBID = BC.TableBID
AND BC.MEMBERID NOT IN ( SELECT MemberID from Member Where MemberNo = A.MemberNo )

Upvotes: 2

Related Questions