Hello all I have a very simple update that I need to do, but it requires me to make a join on another table or two:

Here is an query that I had initially but that gave me an error

update Quote q, Employee e set q.EmployeeID = null where q.EmployeeID = e.Id and q.EmployeeID is not null and q.IsProcessed = 0 and e.Department.Id IN (1,2)

This gives me an error stating that "Expected set, but found "," - That is pointing to my reference to the table Employee

Then I decided to alter it by calling createSQLQuery by using native SQL:

update quote q, employee e set q.EmployeeID = null where q.EmployeeID = e.Id and q.EmployeeID is not null and q.IsProcessed = 0 and e.Department IN (1,2)

That gave me an MySQL Exception error by stating that there is no table named "Quote" - that is true because in the database the table "quote" is all lowercase.

I would like to basically do a very simple update - how can I go about creating an update statement that works?

Thanks in advance.

How about this?



You may also want to fiddle around with the nested select to have a better performance.

Farzad-