Export as SQL UPDATEs

[rentalhost]

Initial discussion at: https://www.heidisql.com/forum.php?t=24901

Feature request: implement the ability to export as SQL UPDATEs on the export grid rows dialog, in addition to another modes like SQL INSERTs, SQL REPLACEs and SQL DELETEs/INSERTs.

It should acts like SQL DELETEs/INSERTs (specifically the DELETEs logic part), that requires the PK index to be defined on results grid, else should display the error Cannot compose WHERE clause - column missing: 1%s (transifex hash dddc23307a48479ee39cb81e002025b9). Additionally, the UPDATEs SET should exclude PK index column.

• HeidiSQL version: 9.5.0.5245 (from 2018-02-11)

[cytsunny]

Looking forward to this feature being implemented. This feature would be helpful when cloning data to a row with different condition then primary key.

[ansgarbecker]

Isn't an UPDATE command with all columns except the primary key doing effectively the same as a REPLACE ?

[lukinhaspm]

When we use REPLACE the old row is deleted before the new row is inserted, some times its a problem when we have FKs and Triggers. I tried to use REPLACE when the table has a trigger (when insert) and the new row was created in the target table.
I think this function "export update" its a good idea.

[cytsunny]

Isn't an UPDATE command with all columns except the primary key doing effectively the same as a REPLACE ?

If you are simply cloning, yes.

However, with update statement, the condition can be changed to something else, which would be handy as a way to copy the data out and paste to many columns.

Also, HeidiSQL provide both REPLACE and DELETE/INSERT, which basically do the same thing, so I think similarity is not a point to reject this feature request.

[daath]

I also have this need right now - restoring a bunch of rows from a database - just a portion, not all.

REPLACE doesn't work because of constraints - it's tedious to write a parser to generate UPDATEs from INSERT/REPLACE, but it seems that is my only solution right now :(

[JP95Git]

Any chance that this feature will be implemented? I am testing HeidiSQL at the moment and have to export some data as UPDATE statement. INSERT/DELETE or REPLACE did not work, because I have some constraints on my PK.

[ansgarbecker]

Next build has an SQL UPDATEs option in the grid export dialog, and in the "Copy as" context menu.
That format does - like the INSERT SQL format - exclude auto-increment columns.