db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From der...@os10000.net
Subject using a join in an UPDATE command
Date Fri, 04 Mar 2005 11:49:32 GMT

first of all, let me thank Raj Saini & Benoit Hambucken for their
answers to my first question.  The question for the delete command has
been solved completely, since I have to only make sure that I package
all attributes to be joined into one unique string and then use their
recommended method.

For my second problem, the UPDATE with a join, I have (once again) been
to imprecise in my question, so I will ask once more:

create table keys ( person varchar(10), ky varchar(10) );
insert into keys values ( 'alice', '9876' );
insert into keys values ( 'bob', '54321' );
insert into keys values ( 'charlie', '121212' );
insert into keys values ( 'douglas', '343434' );

create table badies ( person varchar(10), locked_key varchar(10)  );
insert into badies values ( 'charlie', 'ding' );
insert into badies values ( 'douglas', 'dong' );

update keys set ky=badies.locked_key where person=badies.person;

ERROR 42X04: Column 'LOCKED_KEY' is not in any table in the FROM list or
it appears within a join specification and is outside the scope of the
join specification or it appears in a HAVING clause and is not in the
GROUP BY list.  If this is a CREATE or ALTER TABLE statement then
'LOCKED_KEY' is not a column in the target table.

This time I need to transfer two (or more) bits of information: the rows
affected and the value(s) to be used in the update command (the update
command permits several updates at once: update x set a=..., b=...
where ...).

Can you help me with this one, too?


Oliver Seidel

View raw message