RE: SQL update make fields match

Don't know there you found those crib sheets.  But they weren't for DB2/400.

Try

update TableOne
  set TableOne.field1 = (select TableTwo.fieldX
                         from TableTwo
                         where TableTwo.CommonID = TableOne.CommonID
                         )


Note: the above will only work if TableTwo has one and only one row with that 
same CommonID as TableOne.  If TableTwo doesn't have a matching row, the 
TableOne.field1 must be null capable or you'll need to make use of coalease.


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeff Crosby
> Sent: Tuesday, June 21, 2005 3:02 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: SQL update make fields match
> 
> 
> I want to use an SQL update statement to make a field in a 
> file match a
> field in the associated file record.  A crib sheet I found 
> online shows 2
> ways to do it:
> 
> update TableOne
>     inner join TableTwo on TableOne.commonID = TableTwo.commonID 
>     set TableOne.field1 = TableTwo.fieldX
> 
> Or:
> 
> update TableOne, TableTwo
>     set TableOne.field1 = TableTwo.fieldX
>     where TableOne.commonID = TableTwo.commonID
> 
> Is there a preference in picking one of these?  For example, 
> the 2nd one
> says "update" for both table names even though only one is 
> actually being
> updated.  Will there be some inefficiency here because of 
> that?  Like some
> unneeded lock on the file?  Or is the SQL engine smart enough 
> that it will
> "Read Everything Before Doing Anything" and handle it the 
> same as the first?
> 
> Thanks for any insight.
> 
> -- 
> Jeff Crosby
> Dilgard Frozen Foods, Inc.
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
> 
> The opinions expressed are my own and not necessarily the 
> opinion of my
> company.  Unless I say so.
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>