Query to delete from two tables using INNER JOIN

[LukeH]

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;

DELETE
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.

Thanks

Luke.

[ScottR]

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

[LukeH]

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

[ScottR]

You can just combine the two in Run script event handler: