• 0

[MySQL] Views for inserting / updating


Question

I want to learn a bit more about VIEWS.

I understand what they are, and the point of them, but want to learn how to maintain tables using them.

What I mean by that is how to create a view that I can INSERT into (or UPDATE / DELETE) - which in-turn manages the underlying tables.

For example:

people

ID int

Firstname varchar(50)

Lastname varchar(50)

addresses

ID int

AddressLine1 varchar(255)

Postcode varchar(10)

A relevant view would be

SELECT

*

FROM

people

JOIN

addresses

ON

addresses.ID = people.ID

I know this is a lame example, but it would output all people with their addresses.

But I cannot insert into such a view - I get errors: can not modify more than one base table through a join view

Halp?

Link to comment
Share on other sites

13 answers to this question

Recommended Posts

  • 0

You're not really meant to insert into a view since it's not a "real" table. You would have to intercept the insert with a trigger and turn it into an insert into the lower tables.

Link to comment
Share on other sites

  • 0

Please can I get a response other than that. Sorry - but what you posted simply is not true.

It's not commonplace to insert into views, but you very much CAN and moreover, within an MS Sql environment it does exactly what I want.

Link to comment
Share on other sites

  • 0

Actually, I do, very well. I would argue that if you've never utilised view insertion, then you could do with reading up a little further.

Link to comment
Share on other sites

  • 0

from the mysql faq:

23.6.6: Can you insert into views that are based on joins?

It is possible, provided that your INSERT statement has a column list that makes it clear there is only one table involved.

You cannot insert into multiple tables with a single insert on a view.

Link.

Link to comment
Share on other sites

  • 0

Yeah, this was the caveat I was stumbling across, it seems it will only work with views on a single table. I found elsewhere that you can do it on tables with JOINS as long as some primary key constraint is maintained, but I didn't fully get the terminology.

Shame this, as it would have been a perfect solution to our issue - but I have other ideas... :)

Link to comment
Share on other sites

  • 0

you can insert into views, i would strongly advise against it. i have had to support applications that do this, and they can be a bit of a nightmare.

in any case, the fact that you can only use a view that covers a single table, there is little point in using this method.

basically, what you need to do is do a standard INSERT statement, to your view, but then have a trigger on the view to write to the actual table. messy.

Link to comment
Share on other sites

  • 0

I do love how people are posting based upon assumption not knowledge. Really handy!

BGM: Thank you for a relevant post. You are 100% correct it can be a pain but if properly managed can be a useful tool.

However, we have gone down the route of:

A large table with all data being inserted, then triggers to farm the data off to the relevant normalised tables.

Link to comment
Share on other sites

  • 0
I do love how people are posting based upon assumption not knowledge. Really handy!

BGM: Thank you for a relevant post. You are 100% correct it can be a pain but if properly managed can be a useful tool.

However, we have gone down the route of:

A large table with all data being inserted, then triggers to farm the data off to the relevant normalised tables.

i would be cautious using triggers like this.. as you can get into a mess fairly easily here too..

what is your exact scenario, i skim read back up the thread but can't quite see what end result you are trying to achieve.

Link to comment
Share on other sites

  • 0

OK, may be if use if I explain the entire scenario :)

Back-end managment system as used by staff is a C# app based around a Microsoft SQL 2005 database.

Front-end app (online store) is PHP based around a MySQL 5 database.

How to move data back and forth, eg, the inventory of products, associated categories, etc.

Link to comment
Share on other sites

  • 0

I would use SQL Server Integration Services for this without a shadow of a doubt, you have SQL 2005 already so it's not going to be an issue obtaining it..

Both of your databases will be much more supportable in the long run..

of course, there is probably a tipping point where using SSIS isn't effective, but (finger in the air mode) if you are wanting to update more then a couple of tables using your trigger method i would invest some time at least looking at SSIS for sure!

It is built specifically for moving data between disconnected systems :)

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.