Description:
Currently Workbench only allows an update of a single table as defined in the update documentation.
It would be great if you can also update/edit "joined" tables/ more complex queries. This would allow us to define where clauses with other conditions....
How to repeat:
N/A
Suggested fix:
Using MySQL Server parser to breakdown the query/statement in its elements and create an update for a single table using subselects, etc.
Here is an example:
Select A.*, B.*
FROM A JOIN B (On A.PK = B.PK)
Update A JOIN B (ON A.PK = B.PK)
set A.Column3= ZZZ
WHERE B.column3 = XYZ
SQL Breakdown:
Update A
Set A.column3 = ZZZ
Where (A.PK = (Select B.PK From B Where B.column3 = XYZ))
Restrictions:
Views will be difficult (otherwise it would be necessary to break them up too)
Dynamic columns will be difficult (either this can create an error, or you write the updates in a temp table)
In order to reduce the number of bug reports due to challenges (why this works but the other query not), i suggest to add a checkbox to the property tab deciding if this feature is enabled/disabled (default: disabled).