db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Leslie Software <lesliesoftw...@yahoo.com>
Subject Re: Trying to use UPDATE that needs more then one table for its WHERE clause
Date Tue, 01 Nov 2005 11:20:06 GMT
Thanks Ali that must be getting closer - that was the fist time I did
not get a syntax error attempting an update.  The only trouble is it
did not do anthing.  Here is what I executed:
update card set rules_text = session.load_card.rules_text
where card.name in (
	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'
)

session.load_card is my temp table with the new data I am loading.  The
query executes fine and says two rows were affected but when I look at
the card table the rules_text column has not changed.  At fist I
thought I had auto commit turned off or something but I checked my code
and then ran a test with a constant value.  

If I run this instead:
update card set rules_text = 'Test'
where ... <same as above>

The data in the appropriate rows of the card table contain the constant
'Test'.  

The data I want is in the load_card table.  When I execute this select
statement:
select c.name, c.rules_text, lc.rules_text as new_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'

I can see the two cards that have new information along with the old
information and the new information.  So the new data I want updated is
there and the update you suggested works with a constant.  All I need
now is to get the update to use the data from the temp table.

Any idea what else I am missing?

Thanks,

Ian

--- Suavi Ali Demir <demir4@yahoo.com> wrote:

> update card set anythingyourwant where card.name in (
>  
> 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'
> 
> )
> 
> Regards,
> Ali


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



	

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

Mime
View raw message