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
