'How to update a value in column based on value from other table in SQL server?

I have two tables match_score and match_condition I want to update table match_condition and set values of innings, first_4, etc.

Here's the example in which I am trying to update table match_condition with innings value from match_score whenever both of them is not matched



UPDATE a
SET a.innings = b.innings
FROM  match_condition    a
INNER JOIN (SELECT TOP 1 * FROM match_score  order by time_stamp desc ) b  
ON  a.matchid = b.matchid
WHERE  a.innings != b.innings

match_score table


matchid   time_stamp              overs ball  run  team_score   wicket  innings
50821   2021-04-22 10:52:58.640     20  6      1      137         0      1
50821   2021-04-22 10:52:58.637     20  5      1      136         0      1
50821   2021-04-22 10:52:58.633     20  4      0      135         1      1

match_condition table

matchid seriesid    match_time            innings  powerplay_over first_50 first_100 first_150 first_200 first_4 first_6 first_wicket
50820   2780      2021-04-19 18:44:12.360  NULL     NULL            NULL      NULL     NULL      NULL     NULL   NULL     NULL
50821   2780      2021-04-20 10:00:06.827  NULL     NULL            NULL      NULL     NULL      NULL     NULL   NULL     NULL

I made changes according to answers here , but still its showing 0 row affected

sqlsql-server


Solution 1:[1]

If value innings in match_condition will never be -1 this query will finish your job:

UPDATE 
    match_condition 
SET 
    match_condition.innings = a.innings
FROM (
    SELECT TOP 1 innings, matchid FROM match_score ORDER BY time_stamp DESC) a
WHERE 
    match_condition.matchid = a.matchid
AND 
    ISNULL(match_condition.innings, -1) <> a.innings

If you think that value can be somehow -1, just change in ISNULL operator value to sam 'unreal' number.

Here is DB FIDDLE demo

Solution 2:[2]

You can do it by using a subquery

UPDATE match_condition
SET    match_condition.innings =
       (
              SELECT
                     CASE
                            WHEN match_condition.innings != b.innings THEN match_condition.innings
                            ELSE b.innings
                     END
                     FROM match_score b
              WHERE  match_condition.matchid = b.matchid
       )

Solution 3:[3]

The issue with your query would appear to be that != returns not-true if either value is NULL. It is not appropriate.

You could fix your query as written. However, that would only work for one match in the second table. I would instead suggest apply:

UPDATE mc
SET mc.innings = ms.innings
FROM match_condition mc CROSS APPLY
    (SELECT TOP 1 ms.*
     FROM match_score ms
     WHERE ms.matchid = mc.matchid
     ORDER BY time_stamp DESC
    ) ms;

If you wanted to check on the innings value, you could use:

WHERE mc.innings IS NULL

Also note the use of meaningful table aliases. Do not use arbitrary letters for represent tables. Use table abbreviations!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Brugui
Solution 3 Gordon Linoff

Related Questions