Update column in table with data from another column using Join

I am trying to update data from one column to another using a join statement in SQL.

My two tables are rosters and scores. They share playerid. I am trying to add data from scores.opp to rosters.opp.

Not sure what I am doing wrong. When I do the select statement below I am able to see the shared playerid, an empty column in rosters, and the column in scores that has the data I am looking to add to rosters.

SELECT a.playerid, a.opp, b.opp 
FROM rosters a 
JOIN scores b ON a.playerid = b.playerid

When I proceed to do the update I get an error. Here is what my update statement looks like:

UPDATE a
SET a.opp = b.opp
FROM rosters a
JOIN scores b ON a.playerid = b.playerid

I get an error saying, “#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘FROM rosters a JOIN scores b ON a.playerid = b.playerid’ at line 3”

Any help would be appreciated. Thanks!

Answer

You can use the standard sql update with exists as follows:

UPDATE ROSTERS A
   SET A.OPP = (
        SELECT B.OPP FROM SCORES B
         WHERE A.PLAYERID = B.PLAYERID)
 WHERE EXISTS (
    SELECT 1 FROM SCORES B
     WHERE A.PLAYERID = B.PLAYERID)

Please make sure that there is only one record in the SCORES table for matching PLAYERID. if there are multiple records available in the SCORES table for a single PLAYERID then you need to use the aggregate function MAX, COUNT, etc.. in SELECT sub-query (like SELECT COUNT(B.OPP) FROM SCORES B — or MAX)