Обсуждение: how to update with a join?

Hello 

I like to delete "k" from a string with the statement 
update stamm set code = replace (code, 'k' , '');

But the only way to get the canditates I want to update is the following 
statement. It seems that I can not update when I do a join. 

Is there a way around it? With google I found a hint that a subselect
could help but there was no exsample ... And I did not found out how 
to do this.

select a.code 
from stamm a 
where 
a.skart in ( (select skart from stamm where code like '%k%') ) 
and a.code not like '%V%';

I would be happy about a hint.

Thanks a lot 
Michael

Hello all,

thanks for the answere to the prev. thread.  With the help of them 
I solved the problme I used "not like" instead of <> and this leads 
to a problem. This works for me: 

update stamm 
set code = replace (code, 'k' , '')
where code like '%k%' and code <> '%V%';

I would still be interested to learn how to update a row when the result 
set is only reachable via a join.

Thanks a gain
Michael



Michael Hoeller wrote at Samstag, 10. September 2005 18:47:
> Hello
>
> I like to delete "k" from a string with the statement
> update stamm set code = replace (code, 'k' , '');
>
> But the only way to get the canditates I want to update is the
> following statement. It seems that I can not update when I do a join.
>
> Is there a way around it? With google I found a hint that a subselect
> could help but there was no exsample ... And I did not found out how
> to do this.
>
> select a.code
> from stamm a
> where
> a.skart in (
>     (select skart
>     from stamm
>     where code like '%k%')
>     )
> and a.code not like '%V%';
>
> I would be happy about a hint.
>
> Thanks a lot
> Michael

On Sat, Sep 10, 2005 at 18:56:10 +0200, MichaelHoeller@t-online.de wrote:
> Hello all,
> 
> thanks for the answere to the prev. thread.  With the help of them 
> I solved the problme I used "not like" instead of <> and this leads 
> to a problem. This works for me: 
> 
> update stamm 
> set code = replace (code, 'k' , '')
> where code like '%k%' and code <> '%V%';
> 
> I would still be interested to learn how to update a row when the result 
> set is only reachable via a join.

You can use 'FROM' in an update statement to include other tables.
For example (doing a pointless self join):
UPDATE stamm SET code = replace (code, 'k' , '') FROM stamm a WHERE a.code = code
;