× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. June 2005

RE: SQL update make fields match

You are not missing anything - the basics are extremely simple with SQL - you have about half a dozen basic constructs to deal with - FROM, WHERE, ORDER BY, GROUP BY - that are used in several statements - SELECT, UPDATE, DELETE. The complexity comes in understanding your "question", or "query".
BTW, you asked about the UPDATE where you used the comma-separated list & WHERE clause instead of the JOIN syntax - they are the same. The update is defined with the SET statement, the other part is just a different way to define a JOIN. The 400 did not used to have the JOIN keyword.
There are very few cases where you can update through a JOIN, if any, on the iSeries - the manuals will have details. Imagine the difficulty - there has to be a well-defined path through the fields in the JOIN back to the original physical file. I don't think we have ever had update capability through JOIN logical files, and this is the same idea. 

From the SQL Reference on CREATE VIEW:
A view cannot be the object table in an UPDATE statement unless the first SELECT clause contains at least one result column that is derived solely from a column. That is, at least one result column must not be derived from an expression that contains an operator, scalar function, constant, or a column that itself 
is derived from such expressions.


and


A view is read-only if any of the following appear in its definition:
v The first FROM clause identifies more than one table or view.
v The first FROM clause identifies a read-only view.
v The first SELECT clause specifies the keyword DISTINCT.
v The outer subselect contains a GROUP BY clause.
v The outer subselect contains a HAVING clause.
v The first SELECT clause contains a column function.
v The subselect contains a subquery such that the base object of the outer subselect, and of the 
subquery, is the same table.


Enjoy!
Vern

At 03:21 PM 6/21/2005, you wrote:

-snip-



I feel like I'm reaching the dangerous stage with this stuff. <g>

In all honesty, once I really get the syntax down, it's almost scary how
easy this is.  I feel like I'm missing something.

--
Jeff Crosby

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.