Updating a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating a column

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-09 : 17:43:24

I have three tables as follows :

TableA

ID MatchID1 status
1 0001 NULL
2 0002 NULL
3 0003 NULL

TableB

ID MatchID1 MatchID2
1 002 120
2 002 120
3 002 141
4 003 150
5 003 150
6 003 140

TableC

ID MatchID1 MatchID2
1 002 120
2 002 120
3 002 120
4 003 150
5 003 150
6 003 150

Things to consider: populate tableA status with 'FAIL' when following logic is not met:
>1. All MatchID2 for same MatchID1 in table C should be equal to the MINIMUM maatchID2 for same MatchID1 for tableB, if not populate 'FAIL' in the corresponding MatchID1 in tableA

tableC : for 002, ID2 = 120 (this should match to minimum ID2 of TableB which is 120, so its not a fail.)

tableC: for 003, all matchID2 must equal to min ID2 of tableB which is 140, but its not , so its a fail in tableA for corresponding matchID1 in tableA (0003)
so expected table A must be :

ID MatchID1 status
1 0001 NULL
2 0002 NULL
3 0003 FAIL

please note that data might be different but it has to obey the logic.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-10 : 00:50:46

update a
set a.status = 'FAIL'
FROM TableA a
INNER JOIN (SELECT matchID1,MIN(matchID2) AS MinID2
FROM TableB
GROUP BY matchID1) b
ON '0'+ b.matchID1 = a.matchID1
INNER JOIN (SELECT MatchID1,MAX(MatchID2) AS MaxID2
FROM TableC
GROUP BY MatchID1
HAVING MAX(MatchID2) = MIN(MatchID2))c
ON c.MatchID1 = b.MatchID1
AND c.MaxID2 = b.MinID2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-12 : 14:35:56

@visakh16: thanks for reply. However the query is not according to the logic.
for the given sample data, it should fail ID3 in tableA but your query is failing ID2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 00:30:42

sorry its should have been


update a
set a.status = 'FAIL'
FROM TableA a 
INNER JOIN (SELECT matchID1,MIN(matchID2) AS MinID2
FROM TableB
GROUP BY matchID1) b
ON '0'+ b.matchID1 = a.matchID1
INNER JOIN (SELECT MatchID1,MAX(MatchID2) AS MaxID2
FROM TableC
GROUP BY MatchID1
HAVING MAX(MatchID2) = MIN(MatchID2))c
ON c.MatchID1 = b.MatchID1
AND c.MaxID2 <> b.MinID2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources