Collate three tables into one
So I have 3 tables, all have a unique identifier column labeled "seal". They are arranged like this:
containers_tickets_rds:
seal date time etc etc
containers_tickets_ows:
seal date etc etc
containers_dead_seals:
seal date notes etc etc
collated (empty)
seal date1 date2 date3 time etc1 etc2 etc3 notes
The interfaces we use (VFront and Crystal Reports) use the three separate tables for input. What I would like to do is populate the collated table using the first table and add the data from the other two tables after the fact. Here's what I come up with so far:
---
BEGIN
UPDATE collated, containers_tickets_rds
SET collated.rds_can = containers_tickets_rds.rds_can, collated.rds_chassis = containers_tickets_rds.rds_chassis, collated.rds_date = containers_tickets_rds.rds_date, collated.rds_time = containers_tickets_rds.rds_time, collated.rds_gross_front = containers_tickets_rds.rds_gross_front, collated.rds_gross_aft = containers_tickets_rds.rds_gross_aft, collated.rds_gross_front = containers_tickets_rds.rds_gross_front, collated.rds_tare_front = containers_tickets_rds.rds_tare_front, collated.rds_tare_aft = containers_tickets_rds.rds_tare_aft
WHERE collated.seal = containers_tickets_rds.rds_seal;
UPDATE collated, containers_tickets_ows
SET collated.ows_date_in = containers_tickets_ows.ows_date_in, collated.ows_date_out = containers_tickets_ows.ows_date_out, collated.ows_date_in = containers_tickets_ows.ows_date_in, collated.ows_ticket = containers_tickets_ows.ows_ticket, collated.ows_can = containers_tickets_ows.ows_can, collated.ows_tons = containers_tickets_ows.ows_tons
WHERE collated.seal = containers_tickets_ows.ows_seal;
UPDATE collated, containers_dead_seals
SET collated.rds_notes = containers_dead_seals.notes, collated.rds_date = containers_dead_seals.date, collated.rds_broken='TRUE'
WHERE collated.seal = containers_dead_seals.seal_number;
END
---
The first run put everything into the collated table, and I run the above as a procedure with a schedule every night. The problem is, as we add new rows to containers_tickets_rds, they do not get added to collated because we are using UPDATE. Since the main list of seal numbers isn't updated, the other tables do not update either. How do I achieve this?