RE: SQL update make fields match

The where clause you have in the original wasn't for the UPDATE statement; it 
doesn't effect _which_ rows in imimsg get updated.

Take a look at these:

--Original
UPDATE IMIMSG
  SET IMIMSG.IMACRC = <..do something to figure out what value to use from 
DMITMMST..>

--New
UPDATE IMIMSG
  SET IMIMSG.IMACRC = <..do something to figure out what value to use from 
DMITMMST..>
  where imimsg.imitem in <..do something to figure out how to get a list of 
items to update..>



Now can you see that your update didn't have a where clause originally?

For the <..do somthing..> bits of code, you end up using some subselects.  The 
WHERE clauses here affect the respective SELECT, not the original UPDATE.


<..do something to figure out what value to use from DMITMMST..> 
 (SELECT DMITMMST.ACREC
   FROM DMITMMST
   WHERE DMITMMST.ITNBR = IMIMSG.IMITEM
  )

<..figure out out to get a list of items to update..>
 (select imimsg.imitem
   from imimsg, dmitmmst
   where imimsg.imitem = dmitmmst.itnbr
     and imimsg.imacrc <> dmitmmst.acrec
  )


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: Wednesday, June 22, 2005 11:16 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL update make fields match
> 
> 
> > You don't have a where clause in you original update 
> > statement.  That's what you need to add in order to subset 
> > the rows you want to update.
> 
> But I do have a where clause.  The original:
> 
> UPDATE IMIMSG
>   SET IMIMSG.IMACRC = (SELECT DMITMMST.ACREC
>                        FROM DMITMMST
>                        WHERE DMITMMST.ITNBR = IMIMSG.IMITEM
>                        )
> 
> Why is that where not a where? <g>  I think I still don't get it.
> 
> -- 
> 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.