MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » November 2006

Re: Another SQL question.



fixed

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-2014 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