update question
netters,
given that you have two tables that are defined exactly that same,
table1 and table2. where table1 has 5 million rows and table2 has
50000 rows of data. table2 holds data that is to be inserted or
used to update the data in table1. using ISQL only, what is the
best way to accomplish this task. also, i dont want to delete from
the table1 table since we are reporting from this table 24x7.
i have tried the following sql but it runs into long transactions
(we have many large logs....), both tables have indexecs on
wafer_lot and lot_class. we have a 4gl program that handles this
nicely, but it uses rowid (which disappear in 7.x and higher
versions of online), so i dont want to use rowid:
database testing;
-- make updates
update table1
set (
wafer_device,
start_date,
completion_date,
probe_date,
wafer_fab,
mask_set,
mask_set_rev
)
=
((
select
wafer_device,
start_date,
completion_date,
probe_date,
wafer_fab,
mask_set,
mask_set_rev
from table2
where table2.wafer_lot = table1.wafer_lot
and table2.lot_class = table1.lot_class
))
where exists
(
select wafer_lot,lot_class
from table2
where table2.wafer_lot = table1.wafer_lot
and table2.lot_class = table1.lot_class
);
-- get the data to be inserted
select a.*,
(select count(*)
from table1 b
where b.wafer_lot = a.wafer_lot
and b.lot_class = a.lot_class) row_cnt1
from table2 a
into temp tmp_t1 with no log
;
-- insert the new data
insert into table1
select *
from tmp_t1
where row_cnt1 = 0;
--
regards,
+----------------------------------------------------------------------------+
| . . | Bob Baskett |
| ... ... | Software Engineer |
| ..... ..... | Business Systems Integration Group |
| .. ... .. | Semiconductor Products Sector |
| . . . | Mesa, AZ |
| | President, Informix Users Group Of Arizona |
| Motorola, Inc. | |
+----------------------------------------------------------------------------+
| Sun 690MP 4.1.3, Online 5.01, 4.10.UD1 Tools, Fourgen v4.10.UC1 |
+----------------------------------------------------------------------------+