Auto fill collumn

I have an existing collumn in a table that contains a 5 digit number used as
an employee id field.(datatype nvarchar) I would like to auto fill the rows
that are '00000' or Null with an number beginning with X0001 and incrementin
g
by 1 thereafter.( I want the X as a prefix). Any Ideas'
Thanks, RobOn Mon, 31 Jan 2005 13:37:23 GMT, Robb Mann wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the ro
ws
>that are '00000' or Null with an number beginning with X0001 and incrementi
ng
>by 1 thereafter.( I want the X as a prefix). Any Ideas'
>Thanks, Rob
Hi Rob,
Define: "beginning" and "thereafter". Remember that tables are UNordered
sets of data, by definition. There is no "first" or "beginning" row. You
will have to define some ranking to base your numbers on.
Assuming you want to use the column date_entered to base the ranking, and
assuming you have no diuplicates in the date_entered, you could use the
following:
UPDATE MyTable
SET employee_id = 'X' + LEFT ('0000' +
CAST((SELECT COUNT(*)
FROM MyTable AS a
WHERE a.date_entered <= MyTable.date_entered
AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
WHERE COALESCE (employee_id, '00000') = '00000'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is the primary key of the table? SSN? Payroll number? Are you
saying that the table doesn't have a key right now? If you just want an
arbitrary surrogate key then use IDENTITY. If you requirements are more
complex then we'll need some more information: DDL, sample data,
required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||If your table does not have duplicated employees by first name and last name
,
then you can use this approach (if not, use also the department to calculate
the rank):
use northwind
go
create table t (
empid char(5) null,
fname varchar(25) not null,
lname varchar(25) not null
)
insert into t values ('00000', 'a', 'b')
insert into t values ('00000', 'c', 'd')
insert into t values (null, 'e', 'f')
insert into t values ('00001', 'v', 'w')
select * from t order by fname, lname
update
e
set
e.empid = 'x' + right('000' + ltrim(rank), 4)
from
t as e
inner join
(
select
a.fname,
a.lname,
count(*) as rank
from
t as a
inner join
t as b
on b.fname + b.lname <= a.fname + a.lname
and (a.empid is null or a.empid = '00000')
and (b.empid is null or b.empid = '00000')
group by
a.fname,
a.lname
) as f
on e.fname = f.fname and e.lname = f.lname
select * from t order by fname, lname
drop table t
go
AMB
"Robb Mann" wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the r
ows
> that are '00000' or Null with an number beginning with X0001 and increment
ing
> by 1 thereafter.( I want the X as a prefix). Any Ideas'
> Thanks, Rob
>|||On Mon, 31 Jan 2005 14:54:43 +0100, Hugo Kornelis wrote:

>UPDATE MyTable
>SET employee_id = 'X' + LEFT ('0000' +
> CAST((SELECT COUNT(*)
> FROM MyTable AS a
> WHERE a.date_entered <= MyTable.date_entered
> AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
>WHERE COALESCE (employee_id, '00000') = '00000'
(snip)
Correction: I should have used RIGHT, not LEFT.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)