SQL UPDATE table1 row-by-row based on values in table2-CodePudding

I have two tables and I want to UPDATE one table based on the values of another table. With the help of the following SO-post I write a query:

query = f""" UPDATE table1
             SET goal = 
            (SELECT table2.goal FROM table2                                                                    
             WHERE player = table2.player
                AND opponent = table2.opponent
                AND date = table2.date
                AND competition = table2.competition
                AND score = table2.score """

When I execute the query every row of table1 is affected with the same value for goal. However, the desired process is that the query checks row-by-row if there are matching rows and, if so, update the column goal. What am I doing wrong?

CodePudding user response：

You must correlate the subquery with the table that you want to update:

UPDATE table1 AS t1
SET goal = (
  SELECT t2.goal 
  FROM table2 AS t2                                                                    
  WHERE t2.player = t1.player
    AND t2.opponent = t1.opponent
    AND t2.date = t1.date
    AND t2.competition = t1.competition
    AND t2.score = t1.score
);

Or:

UPDATE table1 AS t1
SET goal = (
  SELECT t2.goal 
  FROM table2 AS t2                                                                    
  WHERE (t2.player, t2.opponent, t2.date, t2.competition, t2.score) = 
        (t1.player, t1.opponent, t1.date, t1.competition, t1.score)
);

Note that if a row in table1 does not match any row in table2, the column will be updated to null.
If in this case you don't want the column to be updated use also COALESCE():

UPDATE table1 AS t1
SET goal = COALESCE((
  SELECT t2.goal 
  FROM table2 AS t2                                                                    
  WHERE (t2.player, t2.opponent, t2.date, t2.competition, t2.score) = 
        (t1.player, t1.opponent, t1.date, t1.competition, t1.score)
), goal);

If your version of SQLite is 3.33.0 , you could use the UPDATE..FROM syntax:

UPDATE table1 AS t1
SET goal = t2.goal 
FROM table2 AS t2                                                                    
WHERE (t2.player, t2.opponent, t2.date, t2.competition, t2.score) = 
      (t1.player, t1.opponent, t1.date, t1.competition, t1.score);

CodePudding user response：

From what I understand, this query will only affect the table1 if the table2 have the same values. Do you want to check if any row is the same then update the goal value?

Instead of using AND, you could use OR. This modification will make sure the query will go through if any of the values are similar.

query = f""" UPDATE table1
         SET goal = 
        (SELECT table2.goal FROM table2                                                                    
         WHERE player = table2.player
            OR opponent = table2.opponent
            OR date = table2.date
            OR competition = table2.competition
            OR score = table2.score )"""