Posted by Brian on 04/19/07 11:27

>>> 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
>>
>
> ZeldorBlat" <zeldorblat@gmail.com> wrote
>
>> 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).
>
> In short this has to be done to test the tables to find some errors,
> I'm 99.9% sure the errors are their end and not mine.
>
> Thanks for replaying
>
> Brian
>


Hi Zeldor

I have just tried the statement and I'm getting an error

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'FROM full_orders c
JOIN summary s ON c.dnote = s.dnote' at line 5

I have to say I'm a little confused how this statement works as there
is not WHERE command in there, any ideas? (MySQL client version: 4.1.20)

UPDATE full_orders
SET invoicedate = s.invoicedate,
invoicedatetimestamp = s.invoicedatetimestamp,
invoicenumber = s.invoicenumber
FROM full_orders f
JOIN summary s ON f.dnote = s.dnote


Regards

Brian

[Back to original message]