Make sql view editable

Question

I've made this sql view to combine some tables and it works, but it's not editable (i can't insert, delete, edit) data directly in it, but only if i do so in the initial tables, so is there any way to do the same (edit data) directly into the view?

Create view table4 as
Select table1.firstname, table1.lastname, table2.bodyweight, table3.bodyfat
From table1
LEFT JOIN table2 ON table1.table1_id = table2.table2_id;
LEFT JOIN table3 ON table1.table1_id = table3.table3_id;

Tag your question with the database you are using. But I'm not optimistic. — Gordon Linoff, Oct 10 '17 at 15:27
Are you referring more than one table? Any modifications like UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. — Madhukar, Oct 10 '17 at 15:37
sql is not a particular rdbms - which is what you need to state. I think you may be using MariaDB/MySQL? — Jon Scott, Oct 10 '17 at 15:37
@Madhukar yes i use a bootstrap script in order to see, edit, insert, delete data from the view i created and it must do that for all columns in the view. I've also use this srcipt to achieve that from tables and it works. In the matter of views i can get the data but i can't edit them because they are exactly located in the view and not in a table: here's the bootstrap example https://www.phpflow.com/php/addedit-delete-record-using-bootgrid-php-mysql/ — thanos_zach, Oct 10 '17 at 15:46
Different database engines deal with the issue of updatable views differently. Since you don't know your database engine type, I'm voting to close this question as "unclear what you're asking". — STLDev, Oct 10 '17 at 15:55

score 1 · Accepted Answer · answered Oct 10 '17 at 16:23

1

In MySQL you cannot update a view that has "LEFT JOIN", however if you can convert those to "JOIN" you should be OK.

Create view table4 as
Select table1.firstname, table1.lastname, table2.bodyweight, table3.bodyfat
From table1
JOIN table2 ON table1.table1_id = table2.table2_id;
JOIN table3 ON table1.table1_id = table3.table3_id;

See here for reference to what is allowed in updatable views.

answered Oct 10 '17 at 16:23

Jon Scott

4,144
17
29

Thanks a lot! this works! Unfortunately the reason why i choose left join is because some columns should always be null and this can't be done with the Join statement so can l use something else like full join? – thanos_zach Oct 10 '17 at 16:34
You may need to alter your question above to include more details about what you are trying to achieve. why should some columns always be null? – Jon Scott Oct 10 '17 at 16:39
Ok, because this question is answered i won't change it because it can be usefull for others, but i'm going to make a new question with updatable views keeping null values with my exact project and let you know! – thanos_zach Oct 10 '17 at 16:49
so here's my full project and the exact thing i want to achieve in a new question! https://stackoverflow.com/questions/46672311/updatable-mysql-views-keeping-null-values Glad if you could help there too! – thanos_zach Oct 10 '17 at 17:15

Make sql view editable

1 Answers1

Linked