db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From de...@os10000.net
Subject using a join in UPDATE or DELETE command
Date Thu, 03 Mar 2005 08:22:34 GMT
Hello,

I have asked the same question in the IBM Cloudscape forum, but with a
different wording.  Perhaps one of you can provide a definitive answer.

I wish to do the following

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), age int );
insert into badies values ( 'charlie', 20 );
insert into badies values ( 'douglas', 30 );

update keys set ky='xxx' where person=badies.person and badies.age<25;

ERROR 42X04: Column 'BADIES.PERSON' 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
'BADIES.PERSON' is not a column in the target table.
ERROR 42X04: Column 'BADIES.PERSON' 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
'BADIES.PERSON' is not a column in the target table.
delete from keys where person=badies.person and badies.age>=25;

Do you have any suggestions on how I should construct the update and
delete commands to achieve what I aim for?

I am currently using a very tedious method with a temporary table, into
which I insert carefully crafted fragments that do not include the data
I would simply delete from an existing table.  This is possible, since
the INSERT command does support joins in its select clause.

Thanks so much,

Oliver Seidel




Mime
View raw message