Re: Re: SQL Update Table

[Vern Hamberg <hambergv@xxxxxxxxxxxxxx>]

At 02:37 AM 11/25/97 -0500, Jromeh@aol.com wrote:
>>> >   Does anyone know if it is possible to UPDATE   a field in one table
>with
>>> > a value from second table .
>>> >
>>> > TABLEA(MANY)   to  TABLEB(ONE)
>>> > TRANSNO
>>> > CUSNOA       CUSNOB
>>> > ATEXTDATA              BTEXTDATA
>>> >
>>> > I have an 30 byte text field(ATEXTDATA)  and a Numeric Customer#
>(CUSNOA)
>>> > in    TABLEA.
>>> > I want to update the ATEXTDATA with some information(BTEXTDATA)  from
>>> > TABLEB. (CUSNOB) is the  if the KEY.
>
>Explore something like:
>
>UPDATE TABLEA A1 
>  SET ATEXTDATA = BTEXTDATA 
>WHERE CUSNOA IN (SELECT CUSNOB FROM TABLEB WHERE A1.CUSNOA = CUSNOB)
>
>Not a join, but similar effect with subselect. Sure it works with 
>constant where BTEXTDATA resides, less sure about "exported" variable, 
>perhaps a qualifier for subselect file helps:
>
>UPDATE TABLEA A1 
>  SET ATEXTDATA = B2.BTEXTDATA 
>WHERE CUSNOA IN (SELECT CUSNOB FROM TABLEB B2 WHERE A1.CUSNOA = CUSNOB)

Neither will work—you get one of the following:

>Message . . . . :   Column BTEXTDATA not in specified tables.                
>Cause . . . . . :   BTEXTDATA is not a column of table TABLEA in library     
>  PGMRVMH. If the table is *N, BTEXTDATA is not a column of any table or view
>  that can be referenced.                                                    

or

>Message . . . . :   Column qualifier or table B2 undefined.
>Cause . . . . . :   Name B2 was used to qualify a column name or was   
>  specified as the operand of the RRN, PARTITION, NODENAME, or NODENUMBER 
>  scalar function. The name is not defined to be a table designator in    
>  this SQL statement.  If a correlation name is specified following the   
>  table name in a FROM clause, the correlation name is considered to be   
>  the table designator.  If a correlation name is not specified, the table
>  name is considered to be the table designator.  If using SQL naming and 
>  the table

Cheers

Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN 55401
(612) 371-1111 x480


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---