UPDATE and ORDER BY

[alex44]

The Problem:

(In ACCESS 2007)

I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE.
Is there another way to achieve the same result?

If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:

UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;

I appreciate any help you can offer.

Thanks!

Alex

 

[jdraw]

Please tell us more about your data?
I don't understand the importance or need for an Order By clause.

Order By is typically for presentation/display.
Update is dealing with a set of records where Order should have little or no meaning. It is the constraint(s) [WHERE clause conditions] that help identify the set of records to be updated.

Can you show some sample data? (You may have to zip it since your post count is low)

 

[alex44]

Thank you for your reply!

Unfortunately, I am not at liberty to show any sample data due to the confidential nature of the material - can't even use real table and column names.

What I can say is that one table is at a system level, while the other is at a piece/part level; The query puts the parts in TableB under the corresponding Sytem from TableA. To further complicate the issue, the relationship between the tables that is very 'loose'. Thus the "Like" part of the query.
I'm sorry I can't be more specific or provide solid examples. I wish I could!

Is there no hope?

Thanks again,

Alex