Read-only after inner join (MySQL)

Question

In MySQL workbench (Mac OS), I wanted to join two tables so that I can update the second one. The code I put in was as follows

select f.company, f.remarks, c.pic
from feedback f, customers c
where f.col = c.col
order by f.company;

The output is a read only table, which prevented me from updating table "customers" based on the f.remarks column.

Your advice/suggestion is appreciated. Thank you.

Since I am using MySQL workbench, I wanted to join the tables first, and update the output table by double-clicking the box I wanted to change. Thank you. — Newbie, Dec 29 '14 at 09:07
It seems to be a gui user question. Rows in Mysql workbench do become readonly, when there is a join, because the application does not know, how to build a query to update the fields you want to change. You need to reference the specific rows by ids. — newtover, Dec 29 '14 at 09:16
The FAQ that explains "Read Only" behavior: http://dev.mysql.com/doc/workbench/en/workbench-faq.html#qandaitem-A-3-1 -- basically, you need a PK in the results. — Philip Olson, Dec 31 '14 at 17:30

score 3 · Accepted Answer · answered Jan 06 '15 at 09:10

By hovering above the "Read Only" icon, I got the following message: "Statement must be a SELECT from a single table with a primary key for its results to be editable".

After some research based on the advice given by fellow coders, here are some points to note:

In MySQL workbench, one cannot edit the results obtained from any JOINs because it's not from a single table;
In using SELECT from a single table, the primary key must be included in order for the result to be editable.

Thank you to everyone who contributed to the question. I appreciate it.

Michael Coleman · Answer 2 · 2014-12-29T09:30:04.403

0

The problem is because, as you mentioned, SELECT only returns a "read only" result set.

So basically you cant use MySQL workbench to update a field in a read only result set that is returned when using a JOIN statement.

edited Dec 29 '14 at 09:30

answered Dec 29 '14 at 09:10

Michael Coleman

3,288
3
19
18

1

I think that he want to use MySQL Workbench user interface to click on cell and edit it. – Vasil Nikolov Dec 29 '14 at 09:15
Yes I agree, I reworded answer – Michael Coleman Dec 29 '14 at 09:21
Thank you. But let's say I use the following code: select * from customers; I can still edit the table using workbench. – Newbie Dec 29 '14 at 09:22
But that's not true =) you can, if you select rows from a single table. – newtover Dec 29 '14 at 09:22
Can someone please suggest an alternative solution if SELECT isn't the right way to go? Thanks! – Newbie Dec 29 '14 at 09:23
@newtover agreed, updated answer. Newbie, do you want to take the values of the feedback.remarks column in your result and apply them to the customer.pic? or other column in the customers table? – Michael Coleman Dec 29 '14 at 09:38
No, the entries in feedback.remarks are qualitative, I have to update the customers accordingly. – Newbie Dec 29 '14 at 10:19
So which columns from your JOIN'ed result set were you wanting to apply to whcih column in your customers table? – Michael Coleman Dec 29 '14 at 10:21

score 0 · Answer 3 · edited May 23 '17 at 10:33

0

from what i understand you want to update table "customers" based on a query.
maybe this post will help you:
update table based on subquery of table

edited May 23 '17 at 10:33

Community

1
1

answered Dec 29 '14 at 12:32

belostoky

934
2
11
22

Read-only after inner join (MySQL)

3 Answers3