MySQL Bugs: #56794: EDIT syntax / Limited Table Editing

Bug #56794	EDIT syntax / Limited Table Editing
Submitted:	15 Sep 2010 13:47	Modified:	16 Nov 2011 16:54
Reporter:	Kevin Abel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Similar to #53999, the EDIT syntax that was added for the MySQL Workbench is undocumented and limiting. The functionality from QB should be replicated in that, any single table result set (with proper reference to the PK) can be edited.

Further, QB automatically added the PK to single table queries (even if it was not selected) and prefixed the column heading with an asterisk (*) to show that it was automatically added to allow editing. This functionality is also missed.

How to repeat:
1) Create a test table:
CREATE TABLE `edit_test` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `summary` TINYTEXT NOT NULL,
  `description` MEDIUMTEXT NOT NULL
);

2) Populate with some data:
INSERT INTO `edit_test` (`summary`, `description`)
VALUES ('Foo Bar', ''), ('Bar Baz', 'Some long description');

3) Try to edit the data only from the query
SELECT `summary`
FROM `edit_test`
WHERE `description` = ''
ORDER BY `id` DESC
LIMIT 1;

You cannot edit the data returned by the result set. And using EDIT forces you to edit ALL columns and I syntax does not allow for ORDER BY or LIMIT clauses.

Suggested fix:
1) Document the EDIT syntax that was introduced (it could be of value to people that need a quick way to write an edit table data query)

AND

2) Implement the functionality that was found in QB by adding the PK to queries that lack it and then allowing direct editing of result sets from SELECT statements.

Thank you for the problem report.

bug #60972 is a duplicate

bug #61695 is a duplicate

Agreed!  =)  (My bug was a dup of this.)

My bug (57487) was also a duplicate of this.  Some people I know are still using QB until this feature is included in Workbench.  It is indeed very limiting.

I'd like to point out that this feature request, or rather bug I would call it, is very important and should be added to the roadmap and implemented as soon as possible.

Fixed as of 5.2.36:

+        Added the ability to edit data from a result set after executing
+        a query. If &workbench; determines that a returned result cannot
+        be edited, then a read-only icon will be displayed, and hovering
+        over the icon reveals a tooltip that explains why it cannot be
+        edited.