I have three tables called 'data_a', 'data_b' and 'data_c' which each
have 50 columns. One of the columns in each is 'id' and is used to keep
track of which data in data_b and data_c corresponds to a row in
data_a. If I want to get all of the data in all 150 fields for this
month (for example), I can get it with:
select * from (data_a, data_b, data_c) where data_a.id=data_b.id AND
data_a.id = data_c.id AND timestamp >= '2008-09-01 00:00:00' and
timestamp <= '2008-09-30 23:59:59'
What I need to do is execute this search which might return several
thousand rows and write the same structure into 'new_a', 'new_b' and
'new_c'. What i'm doing now in a C program is executing the search
above. Then I execute:
INSERT INTO data_a (timestamp, field1, field2 ...[imagine 50 of them])
VALUES ('2008-09-01 00:00:00', 'ABC', 'DEF', ...);
Get the ID that was assigned to this row since 'id' is a serial field
and the number is assigned sequentially. Say it comes back as '1'.
INSERT INTO data_b (id, field1, field2 ...[imagine 50 of them]) VALUES
('1', 'ABC', 'DEF', ...);
INSERT INTO data_c (id, field1, field2 ...[imagine 50 of them]) VALUES
('1', 'ABC', 'DEF', ...);
That moves a copy of the three rows of data form the three tables into
the three separate new tables.
From the original group of tables, the id for these rows was, let's
say, '1234'. Then I execute:
DELETE FROM data_a where id='1234';
DELETE FROM data_b where id='1234';
DELETE FROM data_c where id='1234';
That deletes the old data.
This works fine and gives me exactly what I wanted, but is there a
better way? This is 7 SQL calls and it takes about 3 seconds per moved
record on our Linux box.
Any thoughts or suggestions would be appreciated.
Thanks,
Howard