using a join in UPDATE or DELETE command

[de...@os10000.net]

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

Re: using a join in UPDATE or DELETE command

[Raj Saini <ra...@gmail.com>]

You can use the subquery :

update keys set ky='xxx' where person = (select person from badies where 
badies.age <25);

You have to insure that your subquery returns a single record. If this 
is not case, use IN operator instead of  = operator.

(I assume Derby support subqueries. I have not used or tested them)

Raj

 derby@os10000.net 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
>
>
>
>
>  
>

Re: using a join in UPDATE or DELETE command

[Benoit Hambucken <b....@immedia.be>]

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

derby@os10000.net 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
> 
> 
> 
> 
>