Thread: Populate unique column with default value

Hey,

I'm updating the user table in our db to have a new column "username"
as follows.
ALTER TABLE usertable ADD COLUMN username varchar(64)
UPDATE usertable SET username='<extension of current row>' WHERE username ISNULL
ALTER TABLE usertable ALTER COLUMN username SET NOT NULL
ALTER TABLE usertable ADD CONSTRAINT usertable_username_key UNIQUE(username)

I want to essentially do what i have above but need to populate the
username field with some unique value so that the last ALTER will run.I have another column in this table that is
unique,an phone
 
extension, and am hoping to use this as a default username.  Is there
an elegant way i can set the username to be the extension of the
current row or should i just write a little loop that goes threw and
populates my username with the users extension manually for each user
in my db and then run the last ALTER.

Thanks,

Jon.

I guess what i'm looking for is something like the following, but i
don't know how i'd do it.

"UPDATE usertable SET username='SELECT extension FROM usertable WHERE
id=<current row>' WHERE username ISNULL",

Is this possible?

Thanks,

Jon.

On 10/1/07, Jon Horsman <horshaq@gmail.com> wrote:
> Hey,
>
> I'm updating the user table in our db to have a new column "username"
> as follows.
> ALTER TABLE usertable ADD COLUMN username varchar(64)
> UPDATE usertable SET username='<extension of current row>' WHERE username ISNULL
> ALTER TABLE usertable ALTER COLUMN username SET NOT NULL
> ALTER TABLE usertable ADD CONSTRAINT usertable_username_key UNIQUE(username)
>
> I want to essentially do what i have above but need to populate the
> username field with some unique value so that the last ALTER will run.
>  I have another column in this table that is unique, an phone
> extension, and am hoping to use this as a default username.  Is there
> an elegant way i can set the username to be the extension of the
> current row or should i just write a little loop that goes threw and
> populates my username with the users extension manually for each user
> in my db and then run the last ALTER.
>
> Thanks,
>
> Jon.
>

On 10/1/07, Jon Horsman <horshaq@gmail.com> wrote:
> ... Is there an elegant way i can set the username
> to be the extension of the current row ...

UPDATE usertable
SET username = extension
WHERE username IS NULL;

> On 10/1/07, Jon Horsman <horshaq@gmail.com> wrote:
> > ... Is there an elegant way i can set the username
> > to be the extension of the current row ...
>
> UPDATE usertable
> SET username = extension
> WHERE username IS NULL;

Hmm, that simple eh, don't I feel stupid =)

Thanks a ton for the quick response.

Jon.