Help With A Update

[warrenk]

I have two tables. The first table is a product table. the second table is prices. It is a one to many relationship as TableB contains multiple prices for a Supp_ID in TableA. What I need is a SQL that updates TableA.Supp_Lprice and TableA.Supp_Lcompany_id with the lowest price and company from TableB. The key field is Supp_ID.

TableA
------
Supp_ID
Supp_Name
Supp_Description
Supp_Lprice
Supp_Lcompany_id

TableB
------
Company_id
Supp_id
Supp_price
Supp_company_id

 

[carp]

I'm afraid I don't quite understand your requirements.
As stated, you tableA will only have one row. Furthermore, it will be logically corrupted after the first update if the supplier ID changes but the Supp_Name,
Supp_Description, and Supp_Lcompany_id don't!
Suppose your original tableA record looks like:

1 'Acme' 'Our Pals' $5.25 32

Now XYZ Corp lowers its prices. By your problem statement, you want to update the above record with the new ID and price. So your new record would look like:

1 'Acme' 'Our Pals' $4.75 15

which seems completely wrong.

Could you clarify this please? Perhaps a sample scenario would help.

 

[LKBrwnDBA]

Try:

Update TableA
   Set (Supp_Lcompany_id, Supp_Lprice) = (
   Select Supp_company_id, MIN(Supp_price)
     From TableB
    Where TableB.Supp_id = TableA.Supp_ID
    Group By Supp_company_id)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[warrenk]

Thanks LKBrwnDBA....exactly what I was looking for!

Warren