Other than the dangers of assuming the century it can be done.  Make sure 
you don't blank out values that are not in the other file.  That's why I 
use the Inner Join.

CREATE TABLE ROB/TIMEXA 
(DATE8 CHAR (8 ) NOT NULL WITH DEFAULT,
  BIGSELLER CHAR (15 ) NOT NULL WITH DEFAULT) 
INSERT INTO ROB/TIMEXA VALUES('20061110', 'ITEMA')
INSERT INTO ROB/TIMEXA VALUES('20061111', 'ITEMB')
INSERT INTO ROB/TIMEXA VALUES('20061112', 'ITEMC')

CREATE TABLE ROB/TIMEXB 
(DATE6 CHAR (6 ) NOT NULL WITH DEFAULT,
BIGSELLER CHAR ( 15) NOT NULL WITH DEFAULT) 
INSERT INTO ROB/TIMEXB VALUES('061111', 'ITEMQ')

select * from timexA
....+....1....+....
DATE8     BIGSELLER
20061110  ITEMA 
20061111  ITEMB 
20061112  ITEMC 

select * from timexB
....+....1....+..
DATE6   BIGSELLER
061111  ITEMQ 

(Note:  Your date formats may differ.  Like, DATE6 may be MMDDYY and not 
YYMMDD.)
UPDATE timexa a 
    SET bigseller = 
         (SELECT b.bigseller FROM timexB b 
         WHERE substr(a.date8,3,6) = b.date6 
           AND a.bigseller <> b.bigseller) 
    WHERE substr(a.date8,3,6) IN 
         (SELECT substr(a.date8,3,6) from timexa A
          INNER JOIN timexb b 
          ON substr(a.date8,3,6)=b.date6 
          AND a.bigseller <> b.bigseller) 
1 rows updated in TIMEXA in ROB.

select * from timexA
....+....1....+....
DATE8     BIGSELLER
20061110  ITEMA 
20061111  ITEMQ 
20061112  ITEMC 

Rob Berendt




Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact