Update only one record in table with duplicates

[BrotherOmar]

I have a table with line item sales information. Each record = one line item. there are usually several records in the table for 1 company. There is a field in the sales line item table for adjustments.

I have an adjustment file that I want to use to update the adjustments field in the sales table. however, the adjustments are by customer, not the line items.

What I'd like to do is run a query that updates the sales line items adjustment field. but i need to do it in such a way that only 1 record in the line items per customer is updated.

So if customer B had 5 records in the sales line item table and there is an adjustment for customer B in the adjustment table, i only want one of the line item records updated by the adjustment table...not all of them.

is there an easy way to do this?

----
Tell 'em Omar back!

 

[traingamer]

Try using the FIRST keyword in your query.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[BrotherOmar]

I tried using FIRST but was getting some error messages. I'll review and make certain I have the proper spelling, etc.

Thanks

----
Tell 'em Omar back!

 

[IT4EVR]

I'm not sure how your table is oriented but I did something like this:

UPDATE LineItems AS a INNER JOIN Adjustments AS b ON a.CustomerID=b.CustomerID SET a.Adjustment = b.Adjustment
WHERE a.LineItemID = (SELECT MIN(LineItemID) FROM LineItems WHERE CustomerID = b.CustomerID);