How to convert a SQL query into hibernate criteria?

Need SQL Update Query

• This is a continuation of question http://answers.google.com/answers/threadview?id=444306 answered by mathtalk-ga. I closed the question by rating it while I still had more to ask. here is the new question: Now that I have the select query complete, I need to do an update process on the results of the Select query. I now need to update some of the records in the result set of the Select Query we created. The criteria are easy for me to describe but I don't know how to translate it into SQL. Here is a sample of the Select Query results: HotelID ChangedFromID Verified Address Zip 9876 No 123 Main St 45844 8765 No 123 Main St 45844 1234 Yes 123 Main St 45844 The criteria for records to be updated is that within any set of "matching records" - (i.e. those with the same Address and Zip), take the HotelID from the record with field Verified = "Yes" and use it to update the HotelID of the other matching records. AND for each updated record, update the ChangedFromID field with the value of the old HotelID before the update AND update the Verified field to "Yes" I'd like an Update query to create the following result update result: HotelID ChangedFromID Verified Address Zip 1234 9876 Yes 123 Main St 45844 1234 8765 Yes 123 Main St 45844 1234 Yes 123 Main St 45844 In the case that there are multiple records within each matching set with (Verified = Yes), there should be no update performed so that it can be reviewed manually later.

• Answer:

  Hi, jimvet-ga: I'm not sure if we are still dealing with the same table as before, which was called SupplierMaster (at least in the part which dealt with creating an index). First here's a query that does update only those records for which a single "Verified" match exists, per your specification. Below I provide an alternative that does a bit more than this, but in what I think is a conservative approach. In SQL View: UPDATE SupplierMaster AS C, SupplierMaster AS C1 SET C.ChangedFromID = C.HotelID, C.HotelID = C1.HotelID, C.Verified = True WHERE C.Verified = False AND C1.Address = C.Address AND C1.Zip = C.Zip AND C1.Verified = True AND ( SELECT Count(*) FROM SupplierMaster as C2 WHERE C2.Address = C.Address AND C2.Zip = C.Zip AND C2.Verified = TRUE ) = 1 ; What I'd suggest is that this unnecessarily restricts the updating of records to cases where there is only one "Verified" matching row, when what might be allowed is to update as long as there is only one unambiguous HotelID to use (regardless of how many "Verified" rows may currently have that same HotelID). A more "liberal" query to do this last approach would be: In SQL View: UPDATE SupplierMaster AS C, SupplierMaster AS C1 SET C.ChangedFromID = C.HotelID, C.HotelID = C1.HotelID, C.Verified = True WHERE C.Verified = False AND C1.Address = C.Address AND C1.Zip = C.Zip AND C1.Verified = True AND NOT EXISTS ( SELECT * FROM SupplierMaster as C2 WHERE C2.Address = C.Address AND C2.Zip = C.Zip AND C2.Verified = TRUE AND C2.HotelID <> C1.HotelID ) ; Note that instead of requiring that the number of verified matching rows is 1, we use the "exists" clause (in a negative form) to make sure that HotelID among any verified matching rows is unambiguous. The UDPATE syntax for Access is one of the most "non-standard" aspects of this product. Let me know of any difficulties you encounter. I spent quite a bit of time checking the syntax as best I can without "access" to your real tables, and making some educated guesses about the underlying datatypes. regards, mathtalk-ga