SQLite Update same table with Select

Question

I am trying to update a table based on the values in the table already.

Player Fixture

PlayerFId
FixtureFId
Availability
SelectedPosition

I want to update all rows where the player id is the same and take the values from a previous fixture id.

Example data

PlayerFId Fixture FId Availability SelectedPosition
    1           1          N              2
    1           2          U              0
    2           1          A              3
    2           2          U              0

I want to update all the rows for FixtureFId 2 with the relevant PlayerFId data for FixtureId 1.

End Result

PlayerFId Fixture FId Availability SelectedPosition
        1           1          N              2
        1           2          N              2
        2           1          A              3
        2           2          N              3    

I have tried using this SQL but it doesn't map the PlayerFId values correctly

UPDATE player_fixture 
SET 
    Availability = (SELECT Availability FROM player_fixture WHERE FixtureFId = 1), 
    SelectedPosition = (SELECT SelectedPosition FROM player_fixture WHERE FixtureFId = 1) 
WHERE FixtureFId = 2

Answer 1

SQLite does not allow joins in updates. But it does allow you to update multiple columns with a single subquery:

UPDATE player_fixture
       SET (availability, selectedposition) =
            (SELECT pf.availability, pf.selectedposition
             FROM player_fixture pf
             WHERE pf.playerfid = player_fixture.playerfid AND
                   pf.fixturefid = 1
            )
       WHERE fixturefid = 2;

Answer 2

You need to include the player's ID in the WHERE clause of the subqueries so that the result correlates.

UPDATE player_fixture
       SET availability = (SELECT pf.availability
                                  FROM player_fixture pf
                                  WHERE pf.playerfid = player_fixture.playerfid
                                        AND pf.fixturefid = 1), 
           selectedposition = (SELECT pf.selectedposition
                                      FROM player_fixture pf
                                      WHERE pf.playerfid = player_fixture.playerfid
                                            AND pf.fixturefid = 1)
       WHERE fixturefid = 2;

db<>fiddle