RE: SQL update make fields match

How about doing a second test in the inner query, at the moment you are only 
making sure that the key fields are the same..

ie

CODE
UPDATE IMIMSG
  SET IMIMSG.IMACRC = (SELECT DMITMMST.ACREC
                       FROM DMITMMST
                       WHERE DMITMMST.ITNBR = IMIMSG.IMITEM
                       and DMITMMST.ACREC <> IMIMSG.IMACRC
                       );

-----Original Message-----
From: Jeff Crosby [mailto:jlcrosby@xxxxxxxxxxxxxxxx]
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.