db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suavi Ali Demir <dem...@yahoo.com>
Subject Re: Trying to use UPDATE that needs more then one table for its WHERE clause
Date Tue, 01 Nov 2005 13:49:01 GMT
maybe this is what you want?
 
update card set rules_text = (select lc.rules_text from session.load_card as lc where  card.name
= lc.name)
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


Leslie Software <lesliesoftware@yahoo.com> wrote:
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 ... 

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 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