Bug #63681 Edit/Update columns in joined tables
Submitted: 8 Dec 2011 19:50 Modified: 9 Dec 2011 7:04
Reporter: Christian Jacobs Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S4 (Feature request)
Version:5.2.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: multiple tables, Update on joined tables, workbench

[8 Dec 2011 19:50] Christian Jacobs
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).
[9 Dec 2011 7:04] Valeriy Kravchuk
Thank you for the feature request.