Maybe:
update keys set ky='xxx' where person in (select person from badies where age<25);
delete from keys where person in (select person from badies where age<25);
Benoit Hambucken
[EMAIL PROTECTED] wrote:
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
