Export dialog: SQL UPDATE

david.proweb posted 1 year ago in Feature discussion

Currently we have some options to SQL (INSERTs, REPLACEs and DELETEs/INSERTs).

I think that we should have a new option like UPDATEs, that will create a SQL to UPDATE existing data, based on PK.


id | keys
1  | ABC
2  | DEF
UPDATE table SET `keys ` = "ABC" WHERE `id` = 1;
UPDATE table SET `keys ` = "DEF" WHERE `id` = 2;

Note: the SET don't need include the PK.

ansgar posted 1 year ago

I hoped that people use the REPLACE setting for updating. But of course an UPDATE is less destructive and has therefore some advantages, so there may be some use cases for UPDATEs.

But that only works for tables with a primary key, or at least with a unique non-null key. Exporting tables without such a key is possible, but dangerous. The only way is to use all columns in the WHERE clause then, what's basically the same as the data grid does when you edit a row in such a table.

Does mysqldump provide such an UPDATE mode?

david.proweb posted 1 year ago

My need about the UPDATE was real. I get an old backup from a database (three days past) and I recovery some specific columns data. So I disabled all columns except id and two columns that I like to recover (see attached image). You should note that I have used a filter too to limit what I need backup. It should be a very useful tool to backup.

The solution for PK is just make it a requirement. In case of you try to export with UPDATE mode and don't have a "reliable" column (PK), you should just display an error.

Because of leak of this feature, I had to export into XML and find and replace XML data to SQL. haha It was complicated!

1 attachment(s):
  • rw-case
ansgar posted 1 year ago

Hm, I'm not sure if just showing an error to people wanting to make a backup is a good solution. Also, mysqldump or other tools don't seem to offer an UPDATE mode, most probably for the mentioned reasons.

david.proweb posted 1 year ago

Well, if you need export as UPDATE you need of a PK or similar. If you don't have it, you should get an error (on press the export button). And I belive that the REPLACE method does that currently.

I really don't like to requires a new feature, but unfortunatelly none of available options can solve my problem except by the method that I had used (that is not reliable).

Can you think about it carefully, pleeease? :D

ansgar posted 1 year ago

You could be so nice and file that as a new ticket in the Github tracker.

david.proweb posted 1 year ago

Oh, sure. I will do that soon as possible!

david.proweb posted 1 year ago

Issue created at: https://github.com/HeidiSQL/HeidiSQL/issues/125

Additionally: can I help you to manager issues on Github? Like checking issues, post comments, attributing the correct tags, fixing titles etc. I do that with some other projects like PHP Inspections EA. I don't know if you remember me, but I am the Portuguese (BR) translator/manager (and it was me who asked you a lot to be able to offer translations in HeidiSQL). :D

ansgar posted 1 year ago

Yes, of course I remember you from Transifex.

And yes, you're welcome to help out on Github. Just see through the tickets and watch out for some favourite topics for you, in which you might post some good input. I can give you some collaborator access to the repo if you like. I need your username in that case.

david.proweb posted 1 year ago

Yeah, thanks! I am rentalhost on Github. I will read all open issues today and mark it with correct tags (improvement, bug, ...) if you like and give my opinion where possible. Did you intends to use the Milestone feature?

ansgar posted 1 year ago

Most issues are already tagged good enough, but there are some which I was not yet able to look after, with yet no tag.

I don't know nothing about that milestone feature, so I have no intention to use it yet.

david.proweb posted 1 year ago

Oh, basically Milestone will give you a clue about priorities on project. On mine projects I create three milestones types: next version (minor+1), future version (minor + x) and very future version (major + 1.x).

Eg. for HeidiSQL we should have the following milestones:

  • 9.6: features that you will work on next version (in few months);
  • 9.x: features that will works after 9.6, but not needly will be the 9.7. Are some interesting features to be implemented in about 1 year. After you finish the 9.6 you will select some issues from 9.x to put on the (new) milestone 9.7;
  • 10.x: features that you like to implement on a very future (>1 year).

If at some moment you think that an issue should not be on 9.6, you can just move that to next one. So it is not like a rule list, but a very efficient way to control the project roadmap.

Please login to leave a reply, or register at first.