Thanks for the response!  Unfortunately, it is that simple.
 
A basic "Select * from vwMyView" yields an updatable recordset.
 
Adding "Order by Name" to the end does not allow an update.
 
I should mention that the error associated with the lack of update is:
"Insufficient Key Column Information for Updating or Refreshing"
 
I have since futhered my troubleshooting and determined that I actually AM able 
to update the recordset when the order by is applied in some situations.  
Apparently, I can order by any field that is in the view's main table (the 
table that all of the other tables left join off of) and still be able to 
update.  It is when I order by a field that is not from this main table that I 
get the above error and inability to update.
 
I am still at a loss as to how to fix this so that I can order by any field I 
wish.
 
Any input is greatly appreciated.
 
Thanks,
Travis Eland
 
 

________________________________

From: sheeri kritzer [mailto:[EMAIL PROTECTED]
Sent: Thu 5/4/2006 4:15 PM
To: Eland, Travis M.
Cc: mysql@lists.mysql.com
Subject: Re: ORDER BY making recordset non-updatable



Maybe I'm thick....

You have a view, called vwMyView.

You SELECT rows from it, and you're able to update the view?

Yet when you SELECT with an ORDER BY clause, you're not allowed to
update the view?

I just do not understand how a read statement affects DML.  I think
you're going to have to post the query you're using, as it's more
complex than a SELECT.  Perhaps you're using a REPLACE INTO ....
SELECT statement?  Or UPDATE .... WHERE .... IN (SELECT....)?

-Sheeri

On 4/19/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote:
> Heya.
>
> I am in the process of modifying a program to access data from a MySQL 
> database instead of a SQL Server database.  I have a view that is referenced 
> as follows (through use of a data environment command):
>
> Select * from vwMyView where id = ?
>
> If I run this command, I get the data that I would expect, and I am able to 
> update the data that I would expect to update (there are a few joins in the 
> view so there are a couple fields that I understand that I cannot update).
>
> My problem is, if I add an ORDER BY statement at the end of this command, the 
> recordset still returns data, but it becomes non-updatable.
>
> I would include my SQL, but unfortunately it is on a classified machine.  I 
> have verified the SQL numerous times and it works fine in every way except 
> when I use ORDER BY.  The SQL structure (though slightly modified for mySQL) 
> also worked fine in SQL Server.
>
> Is this a known issue?  Is there something that I could possibly be missing?
>
> I apologize for the lack of actual code, but I appreciate any insight!
>
> Thanks!
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to