Help with Statement

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

Re: Help with Statement

On Apr 18, 6:44 pm, "Brian" 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).

Re: Help with Statement

>> 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" 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

Re: Help with Statement

>>> 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" 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