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

Where is this part reflected in he query: "where the player id is the same"? — ZorgoZ
– ZorgoZ, Commented Sep 23, 2018 at 16:36
That's where I'm struggling, I'm guessing it needs to be a self join? — Carl
– Carl, Commented Sep 23, 2018 at 16:37
You can reference the outer table in the inner query, just use an alias. — ZorgoZ
– ZorgoZ, Commented Sep 23, 2018 at 16:38

Gordon Linoff · Accepted Answer · 2018-09-23 17:35:44Z

6

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;

answered Sep 23, 2018 at 17:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@Carl . . . I'm surprised you accepted and then unaccepted this answer.

sticky bit · Accepted Answer · 2018-09-23 17:12:35Z

1

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

edited Sep 23, 2018 at 17:12

answered Sep 23, 2018 at 16:39

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

10 Comments

Carl Over a year ago

This sets both columns to null.

ZorgoZ Over a year ago

@Cael Try setting the alias on the outer table reference not on the inner ones.

sticky bit Over a year ago

It works if adapted to your sample data: dbfiddle.uk/… Maybe there is no fixture with a one less ID in your real data? See my edit on how you can try to cover this.

Carl Over a year ago

I tried setting the FixtureFId to a specific value on the inner query e.g. 1. Why does it have to be minus 1?

Carl Over a year ago

Problem is the fixture id's could be completely random and some previous fixtures with higher value FixtureFId.

|

Collectives™ on Stack Overflow

SQLite Update same table with Select

2 Answers 2

1 Comment

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related