LIMIT 1 prevents modifying data on updatable multi-table view

andersw posted 3 years ago in General

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.)

sumon posted 3 years ago

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?

andersw posted 3 years ago

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.

andersw posted 3 years ago

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.

archmage84 posted 3 years ago

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.

sonicsmooth posted 3 months ago

Hi, has this issue been addressed in any way? I am using version 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.

sonicsmooth posted 3 months ago

BTW this is the same issue as forum thread 11660

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

ansgar posted 3 months ago

Is this an issue in the bugtracker already?

andersw posted 3 months ago

I think one duplicate report is

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

ansgar posted 3 months ago

I meant an issue in the bugtracker, on Github

sonicsmooth posted 3 months ago

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.

ansgar posted 3 months ago

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

sonicsmooth posted 3 months ago

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

Please login to leave a reply, or register at first.