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 14:21:22 GMT
Bingo - that worked.  Yippeee:-)

So for my education using "set rules_text =
session.load_card.rules_text" did not result in a syntax error.  Why? 
Or does not behave as I expected.  The number of rows effected was

The solution you offered included two sub selects (one to provide the
list of cards to update in the update's where clause and one to provide
the values to set in the set clause); is there a section of the Derby
documentation I could read that would help me understand where I can
use sub selects.  The documentation for the update statement looks like
    UPDATE table-Name
        SET column-Name = Value
        [ , column-Name = Value} ]*
        [WHERE clause] |
    UPDATE table-Name
        SET column-Name = Value
        [ , column-Name = Value ]*

I have used sub selects in where clauses before so I might have figured
that out; however my friend that is helping me with my SQL keeps
suggesting I unroll my sub selects; so in my mind I have been avoiding
them lately.  I guess I have to learn when they are appropriate and
when they are not.  Did I miss the place where 'Value' is defined?  I
admit I did not read the whole reference document.  I am quite new to
SQL as well as Derby so I am learning as I go.

Thank you very much for your help.  I hope you don't mind my asking a
few more questions to solidify my understanding.


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

> 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

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


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

View raw message