RE: SQL update make fields match

I will usually condition the sub query to only select the records that
need updated, based on the values from either a list or a range of
values that are to be updated from the file that is being used to
reference the records that need updated.

I have found that if you try to updated records and there are multiple
values in the update file you have to add another sub query to make sure
your only updating the records that you really want to update from the
sub query file.

update f42119 a set SDMOT =
(Select SHMOT from MOTFIX where sdkcoo = shkcoo and sddoco = shdoco
and sddcto = shdcto and sdmcu = shmcu) where
concat(concat(concat(sdkcoo,digits(sddoco)),sddcto),sdmcu)
in (select
concat(concat(concat(shkcoo,digits(shdoco)),shdcto),shmcu)
from MOTFIX)

This will only update the records in the F42119 file that exist in the
MOTFIX file.

Hope this makes since.

David McCune
SR. Programmer Analyst
Fike Corp
Blue Springs, MO
816-655-4577

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] 
Sent: Wednesday, June 22, 2005 8: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.