Question about "delete row" in a JOINED SQL query output

Hi there,

I,ve got a simple question about HeidiSQL's "delete row" function.

When displaying a complete table or selected entries of that table, it is clear what the "delete selected row" function will do.

In my case, I have some code which will query one table and get some missing parts of information of another table by using a LEFT JOIN statement in my query.

So, what will happen if I use "delete selected row" on that output? Will it delete only the entry in the primary table (the 1st in that query), or will it also delete the data in the JOINED table?

Please help me to understand before running into a problem...

Thanks, Alex

You will be asked before HeidiSQL deletes a row. So you could just try and see what's happens.

In results with more than one table, HeidiSQL will display an error when trying to delete a row, saying "more than one table involved" or something. This is a problem itself, and I planned to solve that, as it should be quite possible to delete a joined row. However, you should be save to click "Delete row".

Hello Ansgar,

thanks for letting me know. So I'll have to use a query that will not involve a JOIN to delete a row from a table for now, which is fine for me.

From my perspective, an ideal solution would be that HeidiSQL will - in a joined SQL output - delete the row which belongs to the selected field.

In the attached image, it would delete the row with ID 3,104,404 from table volkszaehler.data.

With best regards, Alex

You also have the option of using the data tab on the specific table, or use a query without a join.

Your proposed solution would indeed be nice. However, that would a) require table name per column support in grids, and b) require the user to understand what he's deleting in such cases. I'm nearly sure a can be done, but not b could be a problem, or?

Hello Ansgar,

You also have the option of using the data tab on the specific table, or use a query without a join.

While I found the data tab doesn't provide me with the combined information and is not optimal for large tables (the "data" table in my DB holds hundreds of thousands of different measurement values and will further grow), yes, there are ways to get the information without a JOIN.

I.e. the following query will provide me with the same information as the JOIN does...

SET @CHANNEL = 3;

SELECT channel_id AS Kanal,(SELECT VALUE FROM `volkszaehler`.`properties` 
  WHERE  `entity_id` = @CHANNEL AND `pkey` = 'title') AS Name,VALUE AS Wert,TIMESTAMP,FROM_UNIXTIME(TIMESTAMP / 1000 ) AS Echtzeit,id AS SQL_ID
FROM `volkszaehler`.`data` 
WHERE `channel_id` =  @CHANNEL and TIMESTAMP> (unix_timestamp(current_timestamp)-24*60*60)*1000

ORDER BY timestamp DESC
;

However, this brings me back to my original question: In this case also two tables are involved - will a "delete line" on the output delete the affected row from volkszaehler.data only and leave the legend from volkszaehler.properties in place?

Your proposed solution would ... require the user to understand what he's deleting in such cases.

You are right with that. Perhaps a pop-up confirmation message would help? Like "You are about to delete complete line ... from table ... Are you sure?"

Thanks for looking into this! -Alex

Hi!

So, today I had the need to delete a wrong measurement value from the volkszaehler.data tabe, using the output I got from the query in my last post, which I rewrote to not contain a JOIN.

I've got two identical errors - one about the "Name" value which was the result of a subquery to another table, and one almost identical complaining about the date value being a result of the FROM_UNIX_TIME function.


Do you see any way to make that work, without removing that important two columns from my query each time I need to perform a delete?

Thanks in advance, Alex

Yes, there should be some way, I just need to have it in a ticket on Github (see above "Bugtracker" link, then log on and click "New issue"). And of course we need some idea how to hint the user about multiple tables, and whatever he's about to delete.

Hi Ansgar,

I just submitted two different issues (838, 839) to GitHub, because two different kind of scenarios are involved and may require different handling.

Thank you for your attention! -Alex