Posted by ZeldorBlat on 04/18/07 23:09

On Apr 18, 6:44 pm, "Brian" <brian_NOSP...@nrwp.co.uk> wrote:
> Hi all
>
> I know this can be done, just not sure how
>
> I have 2 tables of orders, table A ("summary") has 1 line of info per order
> (a summary the orders)
> table B ("full_orders") has the full details of orders, this is a one to
> many relationship
>
> I have been asked to take the "invoice_number", and "invoice_date" from the
> "summary" table and add it
> to the matching fields in the "full_orders" matching it by a field called
> "dnote", I just can't think of how to do it.
>
> I know there is a join in here somewhere but I just can't get mat head
> wrapped round it, I could rite a script to loop
> though this but there has to be a better way.
>
> What I am trying to say is this
>
> Start at being of summary table and loop though each row
> Get "invoice_number", "invoice_date" and "dnote", from "summary" table
> UPDATE "full_orders" SET invoice_number = summary.invoice_number,
> invoice_date = summary.invoice_date WHERE dnote = summary.dnote
> end loop
>
> Can anybody help
>
> Brian

update full_orders
set invoice_number = s.invoice_number,
invoice_date = s.invoice_date
from full_orders f
join summary s on f.dnote = s.dnote

I'm curious why you want to store the invoice_number and the
invoice_date in both places. Normalization tells us that it should
only be in one (probably the "summary" table).

[Back to original message]