UPDATE Data in a VIEW

I have a VIEW which joins three tables. I am able to UPDATE using this SQL-Query in the query-window:

UPDATE test SET OXVALUE_O2A='1' WHERE OXID_O2A='d46b831aa315c438e18c0965cd10fb9f' LIMIT 1;

When attempting to enter a value directly in Heidi's VIEW-grid, I receive an error "0 rows updated when that should have been 1".

Does anybody know how to resolve this? Thanks a lot.

HeidiSQL checks the number of affected rows from the last query, and then displays that "0 rows updated when..." message in case that was 0. The reason is mostly some silent server side conversion of data. You should fire the automatic SQL query from the log panel in the query tab. Then check why it affects 0 rows only.

Hello Ansgar,

thanks for your answer. I have checked the following:

When attempting to enter a value directly in Heidi's VIEW-grid, Heidi generates this query:

UPDATE db365289_4.test SET OXVALUE_O2A='0' WHERE OXID_O2A='d46b831aa315c438e18c0965cd10fb9f' AND OXTITLE_ART='Yura 22 Disc OS' AND OXTITLE_ATT='Werkzeugaufnahme' AND OXVALUE_O2A='' AND OXPOS_ATT=5600 LIMIT 1;

This produces the error. When running that same query from the query tab, there is no error but as before, no row is affected.

I then took my "working" query, which is still:

UPDATE test SET OXVALUE_O2A='1' WHERE OXID_O2A='d46b831aa315c438e18c0965cd10fb9f' LIMIT 1;

and added Heidi's syntax succeedingly. It worked well until adding any further AND element:

• this works:

UPDATE db365289_4.test SET OXVALUE_O2A='0' WHERE OXID_O2A='d46b831aa315c438e18c0965cd10fb9f' AND OXTITLE_ART='Yura 22 Disc OS' LIMIT 1;

• this does not:

UPDATE db365289_4.test SET OXVALUE_O2A='0'WHERE OXID_O2A='d46b831aa315c438e18c0965cd10fb9f' AND OXTITLE_ART='Yura 22 Disc OS' AND OXTITLE_ATT='Werkzeugaufnahme' LIMIT 1;

If OXID_O2A is a unique column in that table, why not create a primary key on that column? HeidiSQL takes all columns into a WHERE clause if the table has no primary or unique key. After defining a primary key, HeidiSQL will use the column(s) of that key into the WHERE clause, which should solve your problem.

OXID_O2A is already the primary key in the referring table. Do you mean to create a primary key in the VIEW? I would not know how to do that.

May be it is useful to show how I created my VIEW:

select oxobject2attribute.OXID AS OXID_O2A,oxarticles.OXTITLE AS OXTITLE_ART,oxattribute.OXTITLE AS OXTITLE_ATT,oxobject2attribute.OXVALUE AS OXVALUE_O2A,oxattribute.OXPOS AS OXPOS_ATT from ((oxobject2attribute join oxarticles on((oxobject2attribute.OXOBJECTID = oxarticles.OXID))) join oxattribute on((oxobject2attribute.OXATTRID = oxattribute.OXID)))

Thank you.

Oh I see. A view does not have keys, at least not as far as HeidiSQL looks around. So I guess you'd be better off to edit the data in the underlying table, not in the view. That way HeidiSQL uses the primary key.

I think it is the same problem as in my postin "HEIDI" from 2017-01-05 HeidiSQL generate automatic a Update SQL without the first keyfield from the Primery Key. You are lucky that you have not destroyed anything

Hello Ansgar,

I created that VIEW because those three tables separately don't show what I need for editing. As I see it, Heidi's automatically created SQL does not offer editing VIEWS that way and I guess there is no way to modify it. Which should not mean that Heidi isn't still an awfully well done application.

@sqluser: this seems to me another issue.