Pg Update using query on secondary table

[shalabgoel]

Hello,

I have two tables in Postgres:

create table ex_status (
   ex_id     varchar,
   i_id	varchar,
   role     	varchar
);

create table task_status (
    ex_id     	varchar,
    task_id 	varchar,
    i_id               varchar,
    status     	varchar,
    timestamp  	int
);

insert into ex_status (ex_id, i_id, role) 
    values ('1', 'inv1', 'ASSIGN'),('2','inv1', 'YO');

insert into task_status (ex_id, task_id, i_id, status, timestamp) 
    values ('1', '1', 'inv1', 'START', 1),('1', '2', 'inv1', 'CREATE', 2),
	       ('2', '3', 'inv1', 'START', 1),('2', '4', 'inv1', 'CREATE', 2);

Looking for insights on how to update records in task_status table based on conditions on ex_status table using SqlBoiler.. I can write something like the following in SQL.

UPDATE task_status 
SET status = 'CREATE', timestamp = 5 
FROM ex_status
WHERE task_status.ex_id = ex_status.ex_id
  AND ex_status.role = 'ASSIGN' 
  AND task_status.status != 'CREATE'
RETURNING i_id, timestamp

Thanks

[stephenafamo]

You can fallback to using a raw query in this case https://github.com/volatiletech/sqlboiler?tab=readme-ov-file#raw-query