LIMIT 1 prevents modifying data on updatable multi-table view

I have created a simple view using an inner join on a common field of two tables. The join field is the primary key in each of the two underlying tables. This view is "updatable" according to mySql.

I would like to be able to modify a field value through the view data grid in HeidiSQL. The field I would like to modify is not a key. Only one underlying table would be touched so this should be legal.

When I try this generates an UPDATE query which includes a LIMIT 1 at the end. This results in the SQL error / SQL Error (1221): Incorrect usage of UPDATE and LIMIT /

If I run the query manually without the LIMIT 1 the query succeeds with no problem.

Is there any way to remove the LIMIT 1 from the query generated by HeidiSQL in such a case?

(I could provide a full example, but I hope the issue is clear enough from this description.)

I don't think it is a problem with HeidiSQL.its more like rejected from SQL server you are using.Did you try with phpmyadmin /console?

It is definitely a problem with the HeidiSQL client because the illegal query is generated by the HeidiSQL client as the way to effect the update after I modify a field value in the data grid for the view and click elsewehere. I am using mySQL and their docs clearly state that LIMIT can not be included in an update using multi-table syntax:

"For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used."

As I said, I can easily effect the update on the view by writing an UPDATE query, for example writing the same one that HeidiSQL generated but without the LIMIT 1 at the end.

So: Heidi doesn't work to modify field values in such a view through its grid interface -- which is what I would like -- even though they are easily modifiable through different raw SQL.

Oh also: I do not use phpmyadmin.

I realize it is tricky to update data through a view: a view does not have a primary key, and a view need not even include any of the primary keys on the underlying tables -- so a view might have duplicate rows, and if you updated only one of the duplicate rows in the view, there would be no way to know which row to update in the underlying table.

Still in my case I have the keys for the underlying tables in my view, so it should be possible to write a change in a row of the view through to the underlying tables as an update without the LIMIT 1.

Seconded. This is a pain since I'm trying to use views as a substitute for editing tables. I can't use the tables themselves since I need to be referring to the external data as I edit.

andersw is totally right; it's entirely HeidiSQL's fault.

Hi, has this issue been addressed in any way? I am using version 9.5.0.5453 and the LIMIT 1 issue continues to prevent me from updating views that are otherwise updateable.

I have verified this using another client, SQLyog, and it works there. I prefer HeidiSQL as it's pretty awesome, but will use another client if need be, for just this one issue.

My workaround is to create another view which does not do the JOIN and only shows data from one table. This may be good enough for now, but is kind of awkward.

BTW this is the same issue as forum thread 11660

but I found this thread had been going a while ago.

Is this an issue in the bugtracker already?

I think one duplicate report is https://www.heidisql.com/forum.php?t=25570

The general issue is that Heidi can't update data in an updateable view.

I meant an issue in the bugtracker, on Github

I could not find this in the bugtracker. I'm not sure what the policy is on random users (i.e., me) filing bugs. I'll create one if that's okay. thanks.

Yes, please create a new issue there, and please follow what the issue template asks for.

Ok, three weeks later, a bug has been filed! :)