The HQL does not work for me when all of the following are true :
1. it is an Update
2. the entity has a secondary table
3. the where has the primary key.

The query in question is :

UPDATE Customer c SET c.name = 'sumanta' where c.id = 1

This generates a sql :

insert
into
HT_CUST
select
customer0_.ID as ID
from
CUST customer0_,
SEC_CUST customer0_1_
where
customer0_.ID=customer0_1_.ID(+)
and ID=1


"and ID=1" causes the error ORA-00918: column ambiguously defined.


[b]Hibernate version:3.2.5.ga

[b]Mapping documents:


package com.titan.domain;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.SecondaryTable;
import javax.persistence.Table;

@Entity
@Table(name="CUST")
@SecondaryTable(name="SEC_CUST")

public class Customer {
private int id;
private String name;
private int cosid;

@Id
@Column(name="ID")
public int getId()
{
return id;
}
public void setId(int pk)
{
id = pk;
}

@Column(name="NAME")
public String getName()
{
return name;
}
public void setName(String str)
{
name = str;
}

@Column(name="COSID", table="SEC_CUST")
public int getCosid() {
return cosid;
}

public void setCosid(int cosid) {
this.cosid = cosid;
}


}


[b]Code between sessionFactory.openSession() and session.close():

public void createCustomer(Customer customer) {


EntityManagerFactory factory = Persistence.createEntityManagerFactory("titan");
EntityManager manager = factory.createEntityManager();
EntityTransaction utx = manager.getTransaction();



try{

utx.begin();

manager.persist(customer);

utx.commit();

utx = manager.getTransaction();

utx.begin();

int retVal;

String s = " UPDATE Customer c SET c.name = 'sumanta' where c.id = 1 ";

Query query = manager.createQuery(s);

retVal = query.executeUpdate();

Customer cust = manager.find(Customer.class, customer.getId());

utx.commit();


} catch (Exception e) {

e.printStackTrace();

} finally {
manager.close();
}
}



[b]Full stack trace of any exception that occurs:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:630)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:59)
at com.titan.api.TravelAgentBean.createCustomer(TravelAgentBean.java:301)
at com.titan.clients.Client.main(Client.java:24)
Caused by: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:50)
... 2 more
Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:118)
... 7 more


[b]Name and version of the database you are using:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0

[b]The generated SQL (show_sql=true):

Hibernate:
insert
into
CUST
(NAME, DTYPE, ID)
values
(?, 'Customer', ?)
Hibernate:
insert
into
SEC_CUST
(COSID, ID)
values
(?, ?)
Hibernate:
insert
into
HT_CUST
select
customer0_.ID as ID
from
CUST customer0_,
SEC_CUST customer0_1_
where
customer0_.ID=customer0_1_.ID(+)
and ID=1
Hibernate:
delete
from
HT_CUST