Query to sequentially number Null fields in a column

I'm trying to write a Query that will Update all the Null fields in Table1
column1 to 'P' and a 6 digit sequential number starting from 000001
including the leading zeros. Can someone help me figure out the correct
syntax? So far, nothing I've come up with is working right.
TIA
MattWell, if you don't want to add an IDENTITY column, and just want to add the
zero-padded char, you could:
1.) Create temp table with IDENTITY column and primary key from source table
1.) Generate identity values for all rows in target table in the temp table
2.) Update target table to include a zero-padded version of the identity
value
Example:
Let's say your table is called Customer and the primary key is CustomerKey
varchar(10)
BEGIN TRANSACTION
CREATE TABLE
#KeyGen
(
CustomerKey varchar(10) NOT NULL,
NewID int NOT NULL IDENTITY (1,1)
)
INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
WITH(TABLOCKX)
ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
Customer.CustomerKey)
DROP TABLE #KeyGen
COMMIT TRANSACTION
Error handling is an exercise for the reader.
Cheers,
James Hokes
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> I'm trying to write a Query that will Update all the Null fields in Table1
> column1 to 'P' and a 6 digit sequential number starting from 000001
> including the leading zeros. Can someone help me figure out the correct
> syntax? So far, nothing I've come up with is working right.
> TIA
> Matt
>|||The problem is the source table doesn't have a primary key. That's what I'm
creating with this query.
I've been working with this code that I found in the archive, but I can't
get it to work
update temp_Reports tr1
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
I created this table as a temporary test
CREATE TABLE [temp_Reports] (
[identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
And added these values:
1 | Test1
2 | Test2
3 | Test3
Null | Test4
Null | Test5
I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'tr1'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '+'.
but I'm not clear why.
Matt
"James Hokes" <noway@.nospamthanksanyway.com> wrote in message
news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Well, if you don't want to add an IDENTITY column, and just want to add
> the zero-padded char, you could:
> 1.) Create temp table with IDENTITY column and primary key from source
> table
> 1.) Generate identity values for all rows in target table in the temp
> table
> 2.) Update target table to include a zero-padded version of the identity
> value
> Example:
> Let's say your table is called Customer and the primary key is CustomerKey
> varchar(10)
> BEGIN TRANSACTION
> CREATE TABLE
> #KeyGen
> (
> CustomerKey varchar(10) NOT NULL,
> NewID int NOT NULL IDENTITY (1,1)
> )
> INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
> WITH(TABLOCKX)
> ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
> UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
> varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
> Customer.CustomerKey)
> DROP TABLE #KeyGen
> COMMIT TRANSACTION
>
> Error handling is an exercise for the reader.
> Cheers,
> James Hokes
> "Matt Williamson" <ih8spam@.spamsux.org> wrote in message
> news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||>> The problem is the source table doesn't have a primary key. That's what
Make sure, in the future, to declare a primary key at the time of table
definition itself. Also, unless you have at least one set of columns that
are unique in the table, you have no way out.
The error is due to the alias used in the UPDATE clause. Moreover the logic
does not take into account the rows are already NULL. Assuming the second
column is unique within the table here is a workaround:
UPDATE tbl
SET col1 = ( SELECT COUNT( * )
FROM tbl t
WHERE t.col2 <= tbl.col2
AND t.col1 IS NULL )
+ ( SELECT MAX( col1 )
FROM tbl )
WHERE col1 IS NULL ;
Anith|||Matt,
1 -

> update temp_Reports tr1
Can not use alias in this way. Try:
update temp_Reports
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= temp_Reports.identifier_id) + (select
MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
go
2 -
The code will not give the result you are expecting, because the update runs
in a transaction, so the rows updated will not be seen by the "select"
statement that is doing the counting.
AMB
"Matt Williamson" wrote:

> The problem is the source table doesn't have a primary key. That's what I'
m
> creating with this query.
> I've been working with this code that I found in the archive, but I can't
> get it to work
> update temp_Reports tr1
> set identifier_id = (select count(*) from temp_Reports tr2
> where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
> FROM temp_Reports)
> Where identifier_id is Null
> I created this table as a temporary test
> CREATE TABLE [temp_Reports] (
> [identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> And added these values:
> 1 | Test1
> 2 | Test2
> 3 | Test3
> Null | Test4
> Null | Test5
> I get
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'tr1'.
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> but I'm not clear why.
> Matt
> "James Hokes" <noway@.nospamthanksanyway.com> wrote in message
> news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
>
>