Query to delete from two tables using INNER JOIN

Hello all

I am new retool and I am making good progress creating my first mobile app. I am having a bit of difficulty with a query to delete records from two tables when a button is pressed.

I have the button working for deletion of records from one table but I can't get the query to work on multiple tables at once.

Tables = assets & history
Tables are linked via asset_id in both tables

Query that I am trying to write;

DELETE assets, history
FROM assets INNER JOIN history ON history.asset_id = asset_id
WHERE asset_id = {{assetView1.selectedItem.asset_id}};

Query that works on single table;

FROM assets
WHERE asset_id = {{assetView1.selectedItem.asset_id}};

I would like to have a button that, once pressed, deletes an "asset" and all linked history items that have a matching asset_id.



Just run another delete after the initial one //assuming asset_id is a fioreign key in hostory table...
FROM history
WHERE asset_id = {{assetView1.selectedItem.asset_id}};

I assume I would need to create a separate query and link it as a separate event handler for the button?

You can just combine the two in Run script event handler:
Screenshot 2023-10-09 at 10.55.51 AM