how can i apply filter on related column when using update method

[keinagae]

i have a TableA that has a foreign key to TableB and i want to use TableA.update(update).where(TableB.col==some) in where i want to filter based on value in TableB column any idea how can i do that?

[dantownsend]

Here's an example you can run in piccolo playground run:

await Band.update({Band.popularity: Band.popularity + 5}).where(Band.manager.name == 'Guido')

Which is for the following schema:

from piccolo.table import Table
from piccolo.columns import ForeignKey, Integer, Varchar


class Manager(Table):
    name = Varchar(length=100)


class Band(Table):
    name = Varchar(length=100)
    manager = ForeignKey(references=Manager)
    popularity = Integer()