CODEWITHSUNDEEP
sqlsql-server
Shubh
Shubh

Reputation: 593

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

Upvotes: 0

Views: 114

Answers (3)

Marko Ivkovic
Marko Ivkovic

Reputation: 1290

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270883

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!

Upvotes: 1

Brugui
Brugui

Reputation: 584

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
       )

Upvotes: 2

Related Questions