pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to update with a join?
3+ messages / 3 participants
[nested] [flat]
* how to update with a join?
@ 2005-09-10 16:47 Michael Hoeller <MichaelHoeller@t-online.de>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Hoeller @ 2005-09-10 16:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to update with a join?
@ 2005-09-10 16:56 MichaelHoeller@t-online.de
parent: Michael Hoeller <MichaelHoeller@t-online.de>
0 siblings, 1 reply; 3+ messages in thread
From: MichaelHoeller@t-online.de @ 2005-09-10 16:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to update with a join?
@ 2005-09-10 18:03 Bruno Wolff III <bruno@wolff.to>
parent: MichaelHoeller@t-online.de
0 siblings, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2005-09-10 18:03 UTC (permalink / raw)
To: MichaelHoeller@t-online.de; +Cc: pgsql-sql
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
;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-10 18:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-10 16:47 how to update with a join? Michael Hoeller <MichaelHoeller@t-online.de>
2005-09-10 16:56 ` MichaelHoeller@t-online.de
2005-09-10 18:03 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox