MySQL Forums
Forum List  »  Newbie

Collate three tables into one
Posted by: Chris Huchel
Date: December 07, 2017 02:55PM

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?

Options: ReplyQuote


Subject
Written By
Posted
Collate three tables into one
December 07, 2017 02:55PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.