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.

Though you do have a where clause in the subselect statement plus an additional 
one in the subselect used in the where clause I added to the update statement.

Maybe this will help you picture it:

UPDATE IMIMSG
  SET IMIMSG.IMACRC = <..subselect1..>
  where imimsg.imitem in <..subselect2..>




<..subselect1..> = (SELECT DMITMMST.ACREC
                    FROM DMITMMST
                    WHERE DMITMMST.ITNBR = IMIMSG.IMITEM
                    )

<..subselect2..> = (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 9:58 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL update make fields match
> 
> 
> > Jeff,
> > 
> > Depends on what percentage need updating..
> > 
> > If 10%, the yes it would (probably) be better to only update 
> > the ones that need it.
> > If 90%, the it would be (probably) be better to update all of them.
> 
> OK, that makes sense.  That's how I would expect it in RPG land.
> 
> > As far as checking first, here's an example:
> > UPDATE IMIMSG
> >   SET IMIMSG.IMACRC = (SELECT DMITMMST.ACREC
> >                        FROM DMITMMST
> >                        WHERE DMITMMST.ITNBR = IMIMSG.IMITEM
> >                        )
> >   where imimsg.imitem in (select imimsg.imitem
> >                           from imimsg, dmitmmst
> >                           where imimsg.imitem = dmitmmst.itnbr
> >                             and imimsg.imacrc <> dmitmmst.acrec
> >                           )
> 
> You lost me there.  I append this where clause to my existing 
> statement?  Or
> replace the existing where clause?
> 
> The SQL book I ordered just arrived.  I hope some solid 
> reading will clear
> some of this up.  Thanks again for all your help.
> 
> -- 
> 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.
> 
>