Inserting Specific Value in Query Result

I have a table where I need to insert a unique USERID based on the
USERNAME. Seems pretty simple to me, however I am a novice writing SQL
statements.
My knowledge is very limited, so when responding please do not assume I
know basic code.
I have taken on this task in an emergency situation.
Thanks in advance for your help and please let me know if further
information is needed.DK13 (DericK@.sklarcorp.com) writes:
> I have a table where I need to insert a unique USERID based on the
> USERNAME. Seems pretty simple to me, however I am a novice writing SQL
> statements.
> My knowledge is very limited, so when responding please do not assume I
> know basic code.
> I have taken on this task in an emergency situation.
If it's an emergencty I am puzzled by the fact that you did not provide
more information. The standard recommendation for assistance is that
you post:
o CREATE TABLE statement(s) for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.
This permits anyone who wants to answer your question to easily
copy-and-paste into a query and developer a tested solution.
I realise that you if don't know basic code, this may go over your
head. But you should at least be able to produce some sample input,
and the output from it. "A unique USERID based on the USERNAME" is
quite ambiguous. And supposedly there is a requirement that these
user id follows some pattern. It would also be interesting to know
how these usernames look like. Furthermore, it is also unclear whether
you want the userid added to the query result, or a column added
to the table. (Your subject lines says the former, but the latter
makes more sense.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||OK, let's see if this helps.
The query I ran:
select* from sysdba.HISTORY where USERNAME = ('Cruz, Jane')
The result set looks like this(I've left out several columns):
USERID USERNAME ORIGINALDATE
<NULL> Cruz, Jane 2/6/2006 12:00:05 AM
<NULL> Cruz, Jane 2/6/2006 11:25:00 PM
Now from this result I would like to Insert a userid, such as
'U6UJ9A00003J' where the USERID above = "<NULL>". I have several users
that I need to do this for.
Let me know if this is more useful info|||DK13 (DericK@.sklarcorp.com) writes:
> OK, let's see if this helps.
> The query I ran:
> select* from sysdba.HISTORY where USERNAME = ('Cruz, Jane')
> The result set looks like this(I've left out several columns):
> USERID USERNAME ORIGINALDATE
><NULL> Cruz, Jane 2/6/2006 12:00:05 AM
><NULL> Cruz, Jane 2/6/2006 11:25:00 PM
> Now from this result I would like to Insert a userid, such as
> 'U6UJ9A00003J' where the USERID above = "<NULL>". I have several users
> that I need to do this for.
> Let me know if this is more useful info
You could do:
UPDATE tbl
SET USERID = dbo.createuseridfromname(USERNAME)
WHERE USERID IS NULL
All that remains is to write the user-defined function. Unfortunately,
I cannot do that, because I don't know the rules.
Or are the user ids in fact already defined in a table somewhere?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you, I did some other research and found the same function to be
helpful, much appreciated!!