Hello!
Suppose you have the following two legacy tables:
Code:
CREATE TABLE dvds (
dvdid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(100),
language VARCHAR(10),
price DECIMAL(9,2),
movieId INTEGER NOT NULL # <--- not unique!
);
CREATE TABLE moviepics (
movieId INTEGER NOT NULL,
idx SMALLINT UNSIGNED NOT NULL,
url VARCHAR(255) NOT NULL
);
and this Hibernate mapping:
Code:
<hibernate-mapping>
<class name="DVD" table="dvds">
<id name="dvdId"/>
<property name="name"/>
<property name="language" />
<property name="price" />
<property name="movieId" />
<list name="moviepics" table="moviepics" >
<key foreign-key="movieId" column="movieId" />
<list-index column="idx" />
<element column="url" type="java.lang.String"/>
</list>
</class>
</hibernate-mapping>
So far so good.
Now,
( let's say after some legacy update operation that deletes Dvds but does not touch the moviepics table)you want to delete all orphaned moviepics that are no longer referenced from the dvds table.
In
SQL you could do something like
Code:
DELETE moviepics FROM moviepics LEFT JOIN dvds USING (movieId) WHERE dvds.movieId IS NULL;
What would be the corresponding
HQL?
Your help is greatly appreciated.
Cheers!
-- spunky
Login
FAQ
Search