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


Thursday, July 06, 2006

Help building a SQL Statement

SQL Apprentice Question
Two fields: member number and email address

while email address is not null update member number


I need to create a sql statement that will update a VARCHAR member
number from 123456789 to x23456789... is that possible?


Celko Answers

>> Two fields [sic]: member number and email address <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.


>> while email address is not null update member number <<


A member number is usually an identifier and not subject to a computed
change like this. And identifiers are usually fixed length with a
validation constraint. If a member gets an email, what do you do with
his member number?

It sounds like you are trying to over-load member number with
information you already know, thus creating a redundancy. You also do
not seem to have a key in this name-less table. Since email can be
NULL, it is not a key; that leaves member number as the only candidate.
Is that right?

No comments: