Need: Cool method of Syncing two tables
--========================================================================
-- I've got a CHALLENGE for all Informix 6.0 experts.
--
-- I've got a major problem on Informix 6.0 that I cannot figure out
-- a good way to solve. I have developed several work-arounds, but there
-- MUST be a better way.
--
-- My problem is the following:
-- We have a large table of data, table1, and a small table of data, table2.
-- Table2 contains changes which need to be applied to some of the fields in
-- table1. The two tables have identical primary keys. However, table1
-- has more data fields than table2. All the information in table2 must
-- be put into table1 so that table2 can be cleaned out for the next batch
-- of changes. Table1 might be 1 million rows, so full table scans on it
-- are highly undesirable. Table2 might range from 0 to 50,000 rows. I
-- would like to use a method which does NOT perform 50000 separate
-- inserts and/or updates (i.e. a stored procedure), but that is the ONLY
-- way I have found to accomplish the task (without scanning the entire
-- table1).
--
-- The rows from table2 whose keys match rows in table1 need to be used to
-- update those rows on table1. The rows on table2 whose keys no not match
-- rows in table1 must be inserted into table1. I can make the insert work
-- acceptably, but I cannot make the update work acceptably.
--
-- Sybase and Access both have slick syntax which allows a join on an
-- update. Informix 6.0 does not seem to. I have created my problem
-- here as a SQL script so that if anyone can help me, you can recreate
-- my dilemma directly.
--
-- THERE MUST BE AN ANSI-STANDARD WAY TO ACCOMPLISH THIS BASIC DATABASE TASK!
-- (or at least some form of Informix way... maybe it is a slick syntax
-- trick which cues the query optimizer in that an inner join should be
-- performed during the update)
--
Please send any responses to stephen_adkins@aeronomics.com
Thanks in advance!
--==============================================
-- SET UP THE TEST SCENARIO
DROP TABLE table1;
DROP TABLE table2;
CREATE TABLE table1 (
key1 smallint NOT NULL,
key2 smallint NOT NULL,
data1 smallint,
data2 smallint,
data3 smallint,
data4 smallint,
PRIMARY KEY (key1,key2)
);
CREATE TABLE table2 (
key1 smallint NOT NULL,
key2 smallint NOT NULL,
data1 smallint,
data2 smallint,
PRIMARY KEY (key1,key2)
);
INSERT INTO table1 VALUES (1,1,0,0,0,0);
INSERT INTO table1 VALUES (1,2,0,0,0,0);
INSERT INTO table1 VALUES (2,1,0,0,0,0);
INSERT INTO table1 VALUES (2,2,0,0,0,0);
INSERT INTO table2 VALUES (1,1,94,95); -- UPDATE existing row in table1
INSERT INTO table2 VALUES (2,1,96,97); -- UPDATE existing row in table1
INSERT INTO table2 VALUES (3,2,98,99); -- insert new row into table1
--==============================================
-- Sybase UPDATE syntax (does not work on Informix)
UPDATE table1
SET
table1.data1 = table2.data1,
table1.data2 = table2.data2
FROM table2
WHERE table1.key1 = table2.key1
AND table1.key2 = table2.key2
;
SELECT * FROM table1;
-- Reset table1 for the next test
DELETE FROM table1;
INSERT INTO table1 VALUES (1,1,0,0,0,0);
INSERT INTO table1 VALUES (1,2,0,0,0,0);
INSERT INTO table1 VALUES (2,1,0,0,0,0);
INSERT INTO table1 VALUES (2,2,0,0,0,0);
--==============================================
-- Microsoft ACCESS UPDATE syntax (does not work on Informix)
UPDATE table1 INNER JOIN table2 ON
table1.key1 = table2.key1 AND
table1.key2 = table2.key2
SET
table1.data1 = table2.data1,
table1.data2 = table2.data2
;
SELECT * FROM table1;
-- Reset table1 for the next test
DELETE FROM table1;
INSERT INTO table1 VALUES (1,1,0,0,0,0);
INSERT INTO table1 VALUES (1,2,0,0,0,0);
INSERT INTO table1 VALUES (2,1,0,0,0,0);
INSERT INTO table1 VALUES (2,2,0,0,0,0);
--==============================================
-- Ouch! Updates all rows on destination table,
-- setting all non-matching rows to NULL values
UPDATE table1 SET
(data1, data2) =
((SELECT data1, data2
FROM table2
WHERE table1.key1 = table2.key1
AND table1.key2 = table2.key2));
SELECT * FROM table1;
-- Reset table1 for the next test
DELETE FROM table1;
INSERT INTO table1 VALUES (1,1,0,0,0,0);
INSERT INTO table1 VALUES (1,2,0,0,0,0);
INSERT INTO table1 VALUES (2,1,0,0,0,0);
INSERT INTO table1 VALUES (2,2,0,0,0,0);
--==============================================
-- UPDATE WORKS CORRECTLY!!!
-- However, this forces a full-table scan on
-- the target table (table1). This can be
-- devastating when there is a large table of
-- existing information (table1) AND a small SET
-- of modification data arrives (table2).
-- THIS IS UNACCEPTABLE!
-- THERE MUST BE AN EASIER WAY!!!
UPDATE table1 SET
(data1, data2) =
((SELECT data1, data2
FROM table2
WHERE table1.key1 = table2.key1
AND table1.key2 = table2.key2))
WHERE EXISTS
(SELECT *
FROM table2
WHERE table1.key1 = table2.key1
AND table1.key2 = table2.key2);
-- The INSERT step fails because it is not
-- allowed to include the table being updated
-- in a subselect of that INSERT statement.
INSERT INTO table1
SELECT *
FROM table2
WHERE NOT EXISTS
(SELECT * FROM table1
WHERE table1.key1 = table2.key2
AND table1.key2 = table2.key2);
SELECT * FROM table1;
-- Reset table1 for the next test
DELETE FROM table1;
INSERT INTO table1 VALUES (1,1,0,0,0,0);
INSERT INTO table1 VALUES (1,2,0,0,0,0);
INSERT INTO table1 VALUES (2,1,0,0,0,0);
INSERT INTO table1 VALUES (2,2,0,0,0,0);
--==============================================
-- UPDATE WORKS CORRECTLY!!! (as above)
UPDATE table1 SET
(data1, data2) =
((SELECT data1, data2
FROM table2
WHERE table1.key1 = table2.key1
AND table1.key2 = table2.key2))
WHERE EXISTS
(SELECT *
FROM table2
WHERE table1.key1 = table2.key1
AND table1.key2 = table2.key2);
-- WORKS CORRECTLY!!!
-- However, if there is an easier way to do the
-- UPDATE described above, it seems there OUGHT
-- to be a way to do this more elegantly.
-- The use of a temp table seems like a hack.
SELECT key1, key2, data1, data2
FROM table2
WHERE NOT EXISTS
(SELECT * FROM table1
WHERE table1.key1 = table2.key1
AND table1.key2 = table2.key2)
INTO TEMP ins_table2;
INSERT INTO table1 (key1, key2, data1, data2)
SELECT key1, key2, data1, data2
FROM ins_table2;
DROP TABLE ins_table2;
SELECT * FROM table1;
-- Reset table1 for the next test
DELETE FROM table1;
INSERT INTO table1 VALUES (1,1,0,0,0,0);
INSERT INTO table1 VALUES (1,2,0,0,0,0);
INSERT INTO table1 VALUES (2,1,0,0,0,0);
INSERT INTO table1 VALUES (2,2,0,0,0,0);
--==============================================
-- THIS WORKS