SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Saturday, June 16, 2007

newbie question on update

SQL Apprentice Question
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?


Celko Answers
>> 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 recursion was?

Original Source

No comments: