RE: SQL UPDATE using INNER JOIN?

Ron,

You don't need to bother with updating via a join.... 

update table1
   set fldX = (select T2.fldY from table2 T2
               where T1.keyfld = T2.keyfld)

Now, there's still a problem with this.... What happens when T1 does not
have a matching row in T2?  (Answer: you'll update fldx to *null, which
probably isn't what you want...  So, there's two ways to handle; define a
default value that you want to see in place of *null, or only update rows
that exist in both tables.  Since your original notion was to use inner
join, I'll proceed with the latter solution.

update table1
   set fldX = (select T2.fldY from table2 T2
               where T1.keyfld = T2.keyfld)
where exists (select * from table2 T3
               where T1.keyfld = T3.keyfld)  

The other alternative looks like this... Remember that this one will change
every row in T1, so be careful that you understand what's happening...

update table1
   set fldX = (select coalesce(T2.fldY,' ') 
               from table2 T2
               where T1.keyfld = T2.keyfld)

hth,

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Ron Adams
Sent: Monday, January 16, 2006 3:30 PM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: Re: SQL UPDATE using INNER JOIN?


To be more specific, I'm trying to do the equivalent of this on the iSeries:


update table1
   set fldX = T2.fldY
  from table1 T1
inner
  join table2 T2
    on T1.keyfld = T2.keyfld






On 1/16/06, Ron Adams <rondadams@xxxxxxxxx> wrote:
>
> I'm trying to don an UPDATE to a table using data from another table and
> using the INNER JOIN clause. Can anyone show me a working example of this
> because I can't seem to get it right.
>
>