I know that with M$ SQL server, if a view is based on a JOIN and provides a deterministic recordset, then the view could be updateable. But like everything else they put out, if it's a day ending with "Y" and between 10 and 11 AM on the third odd Tuesday of the calendar season, the planets will align and all will be good.(sarcasm)
It's still the better idea to stick with single table queries to define the dataset of an updateable view. However, I have total faith in the MySQL developers that if it's possible, they will make multiple-table updateable views work and work well. If not, well, we will be no worse off. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Rhino" <[EMAIL PROTECTED]> wrote on 12/23/2004 12:12:03 PM: > If MySQL works like DB2 - in most respects, they behave the same - a view > based on a Join is ALWAYS read-only. > > Rhino > > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: "Rhino" <[EMAIL PROTECTED]> > Cc: "mysql" <mysql@lists.mysql.com>; "Nico Alberti" <[EMAIL PROTECTED]> > Sent: Thursday, December 23, 2004 11:54 AM > Subject: Re: Tables "shortcuts"? > > > > Yes, it should be possible to 'alias' an entire table through a view > > (assuming your version of MySQL has views). Views will be "updateable" so > > long as none of the columns are computed. That means that a view based on > > SELECT * FROM tablename should give you two options > > a) you can call your VIEWs columns anything you want (so you can > > match your legacy table's old names) > > b) it will be updateable. You can read from and write to the view > > just as you would have the original table. > > > > HOWEVER!! > > The view will still have to obey any constraints placed on the source > > table. > > You will only be able to insert and update columns presented in the view. > > Base table columns not presented in the view will be "invisible". > > I have no idea if a query using two or more tables can act as the base > > definition of an updateable view. > > > > Another option could be to look at what the developers are calling > > "federated" databases. That works like a "linked table" (to use an M$ > > term) in that you have a table name in your database (local reference) but > > the data actually resides on a different server. Both are coming soon to > > production-ready MySQL. Right now both features are in the testing and > > development phases. > > > > My advice is to check out the new 5.x+ and test it to discover what works > > and what doesn't for what you would like it to do. Perhaps it is stable > > enough to meet your needs but I leave that determination up to you. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > "Rhino" <[EMAIL PROTECTED]> wrote on 12/23/2004 11:30:34 AM: > > > > > > > > ----- Original Message ----- > > > From: "Nico Alberti" <[EMAIL PROTECTED]> > > > To: "Rhino" <[EMAIL PROTECTED]> > > > Sent: Thursday, December 23, 2004 10:25 AM > > > Subject: Re: Tables "shortcuts"? > > > > > > > > > > On Thu, 23 Dec 2004 08:46:13 -0500, Rhino <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > > > I am not very clear on what you want to do. Your English is fine but > > you > > > > > haven't explained the problem and what you want to do in sufficient > > > detail. > > > > > > > > > > > > > Thank you Rhino for your answer. > > > > > > > > I would like to do a thing like this: > > > > > > > > A table, say: olddb.table1 is moved to newdb.table1. Every application > > > > (let's forget about Access now) has to change its query accordingly. > > > > > > > > What I was asking is if there is some trick that can make appear a > > > > "bogus" table1 in olddb that references to the new position of table1, > > > > so any query can work as before affecting the "real" table1 in newdb. > > > > > > > > For what I know this could be a job that can be done by a view (even > > > > if, using 4.1 I can not use them). > > > > > > > > Of course this is not a blocking problem, I was only wondering if > > > > there was a way to save me some work :-) > > > > > > > Nico, > > > > > > It is always best if you post followup questions/remarks back to the > > list. > > > This makes it possible for everyone on the list to follow the > > conversation > > > and to help you. It also ensures that the conversation will be stored on > > the > > > MySQL archive so that others can learn from it in the future. That is > > why I > > > am sending this reply to the list, not just to you directly. > > > > > > I don't know of a way to do what you want to do in MySQL. I am > > relatively > > > new to MySQL myself. I just looked in the manual and didn't see anything > > > that does what you want to do but maybe I just didn't look in the right > > > place. It sounds like you are describing something like a Unix symbolic > > link > > > but I don't know of any way to do that within MySQL. > > > > > > Maybe someone else on the list has an idea that can help you. > > > > > > Rhino > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > >
