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.

The probably is in there since a big consideration is what indexes are usable 
for the query.

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
                          )


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:01 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL update make fields match
> 
> 
> Charles (or anyone),
> 
> > update TableOne
> >   set TableOne.field1 = (select TableTwo.fieldX
> >                          from TableTwo
> >                          where TableTwo.CommonID = TableOne.CommonID
> >                          )
> 
> Question on this.  My statement (working) looks like this:
> 
> UPDATE IMIMSG
>   SET IMIMSG.IMACRC = (SELECT DMITMMST.ACREC
>                        FROM DMITMMST
>                        WHERE DMITMMST.ITNBR = IMIMSG.IMITEM
>                        );
> 
> Every row in IMIMSG is updated whether it was changed or not. 
>  Is there a
> way to only update it if it needs changing?  There would have 
> to be some
> kind of conditional test for this.  In RPG I would check that 
> kind of thing
> before updating because it is much more efficient to only 
> update the records
> that need it.  Is that true in SQL or, because it is 
> set-at-a-time, is it
> more efficient to just do them all?
> 
> Thanks for all your help in this.
> 
> -- 
> 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.
> 
>