newbie question on update

Steven Paul

unread,

Jun 12, 2007, 4:13:14 PM6/12/07

to

I have a clients table which consists of, among other things, a column
named source.

I have a second table, sources, with two columns - client_id and source.

The are fewer rows in sources than in clients. I am trying to update
clients.source with sources.source without affecting the clients that
do not have a corresponding row in sources.

My first try was update clients set clients.source = sources.source
where clients.client_id = sources.client_id

But I ended up with nulls in the clients.source column for rows in
which there was no row in sources.

I am guessing that I need to involve a join somehow, but I am having a
very hard time not thinking procedurally, and just don't "get" it.

I've been looking at Join examples, but anything non-trivial just
baffles me. Would someone be so kind as to explain how to do what I'm
trying to do?

Thanks.

-Steve

Nis Jørgensen

unread,

Jun 12, 2007, 5:58:00 PM6/12/07

to

Steven Paul skrev:

> I have a clients table which consists of, among other things, a column
> named source.
>
> I have a second table, sources, with two columns - client_id and source.
>
> The are fewer rows in sources than in clients. I am trying to update
> clients.source with sources.source without affecting the clients that
> do not have a corresponding row in sources.
>
> My first try was update clients set clients.source = sources.source
> where clients.client_id = sources.client_id
>
> But I ended up with nulls in the clients.source column for rows in
> which there was no row in sources.
>
> I am guessing that I need to involve a join somehow, but I am having a
> very hard time not thinking procedurally, and just don't "get" it.

Since you do not write which database you use, i will assume you are
using postgresql 8.1. In that case you can do

UPDATE clients SET clients.source = sources.source
FROM sources
WHERE clients.client_id = sources.client_id

An approach which should work in any db that supports subselects:

UPDATE clients SET clients.source = (SELECT source FROM sources WHERE
clients.client_id = sources.client_id)
WHERE client_id in (SELECT client_id FROM sources)

Hope these helps

Nis

Steven Paul

unread,

Jun 12, 2007, 10:07:30 PM6/12/07

to

In article <466f16f2$0$90276$1472...@news.sunsite.dk>, Nis Jørgensen
<n...@superlativ.dk> wrote:

> Since you do not write which database you use, i will assume you are
> using postgresql 8.1. In that case you can do
>
> UPDATE clients SET clients.source = sources.source
> FROM sources
> WHERE clients.client_id = sources.client_id
>
> An approach which should work in any db that supports subselects:
>
> UPDATE clients SET clients.source = (SELECT source FROM sources WHERE
> clients.client_id = sources.client_id)
> WHERE client_id in (SELECT client_id FROM sources)
>
> Hope these helps

Yes it does. Thank you very much.
So let me try to understand how it works (using the second version).
First it finds the set of clients who have a corresponding row in
sources (the 'outside' part of the statement), then it updates the
column using the 'inner' select?

I am finding it difficult to think in terms of sets and 'simultaneous'
actions. I keep wanting to solve these things procedurally.

Thanks again.

--CELKO--

unread,

Jun 15, 2007, 12:43:01 AM6/15/07

to

>> I am finding it difficult to think in terms of sets and 'simultaneous' actions. I keep wanting to solve these things procedurally. <<

It takes about a year to have the revelation :) My next book will
deal with thinking in sets. Hey, remember how weird recurson was?