db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Leslie Software <lesliesoftw...@yahoo.com>
Subject Trying to use UPDATE that needs more then one table for its WHERE clause
Date Mon, 31 Oct 2005 17:15:21 GMT
I have an application that needs to update a list of cards from
multiple editions.  The database is setup with a card table which hold
the relavent information about each card; an edition table which holds
the information about each edition and; a card_version table which
keeps track of which edition each card appears.  When a new edition
comes out there could be new cards that have never been seen before (no
problem I am inserting the new ones from a temp table) and some that
are reprinted and have slight changes in their contents.  That updating
of card information from the new edition for cards that already exist
is giving me grief.

Once I have loaded the new edition's cards into a temp table I want to
execute some SQL that will update the card table with the updated
information for those cards that have been reprinted.

Here is the SQL I wrote that can find those cards (the ones printed in
the new edition that are alread in the database from other editions):

--  Retrieve the cards that appear in the newly loaded edition and
already exist in the database
select c.name
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
	e.edition_id = cv.edition_id and
	c.card_id = cv.card_id and
	c.name = lc.name and 
	c.card_id is not null and
	e.release_date < e2.release_date and
	e2.name = 'Test Newest'

When I try to create an UPDATE SQL statement that can update the card
information for those cards I find that Derby's UPDATE does not support
more than one table and I cannot sue JOIN ON either.

After much reading and consulting with those more knowledgable than I
with SQL I found FOR UPDATE OF / WHERE CURRENT OF which I thought I
could use so that my above query could be changed to this: 

select c.name, c.rules_text
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
	e.edition_id = cv.edition_id and
	c.card_id = cv.card_id and
	c.name = lc.name and 
	c.card_id is not null and
	e.release_date < e2.release_date and
	e2.name = 'Test Newest'
for update of rules_text

But it turns out that this is not possible due to the "Requirments for
updatable cursors" which says the query cannot involve more then one
query.

So my question is how do I update my card table with information from
my temporary table where the cards match a certain query?  Can this be
done with SQL or do I have to create code in Java to do it?

Thanks,

Ian


--
Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)



	

	
		
__________________________________________________________ 
Find your next car at http://autos.yahoo.ca

Mime
View raw message