CODEWITHSUNDEEP
sqlsql-server-2012sql-update
Jay C
Jay C

Reputation: 872

Update fields in a SQL Server table sequentially

I have a table AoObject with a column DisplayName. In name are values like Joe Smith, John Smith, Jane Smith. This goes on for thousands of records.

I want to update the table so that starting with record one up to the last record the value of DisplayName is like this. Joe Smith = Customer1, John Smith = Customer2, Jane Smith = Customer3... and so on until all the name columns sequentially say Customer with a number appended to it and not the current value of the column.

This is SQL Server 2012

***The examples below don't work and I've amended my question. My fault for not including enough details. The table is called Aoobject. The field that needs to be changed is actually called DisplayName. I need to filter the result set with something like the following.

Where ObjectDescription in ('Portfolio Description', 'Portfolio Group Description')

AoObject

Upvotes: 0

Views: 1084

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This seems like a bad idea -- the "customer number" should be put into a separate column. It is not part of the name. However, it is easy to do:

with toupdate as (
      select t.*, row_number() over (order by recordnum) as seqnum
      from table t
     )
update toupdate
    set fullname = fullname + ' Customer' + cast(seqnum as varchar(255));

I am assuming that you have some numbering for the records (recordnum) because the question says "with record one up to the last record".

EDIT:

If you want the names to be unique, then append a number only for duplicate names.

with toupdate as (
      select t.*,
             row_number() over (partition by fullname order by recordnum) as fn_seqnum,
             count(*) over (partition by fullname) as fn_cnt
      from table t
     )
update toupdate
    set fullname = fullname + ':' + cast(fn_seqnum as varchar(255));
    where fn_cnt > 1;

This appends a unique number only when it needs to (for fullnames that have duplicates). And, it keeps the cardinality of the number as low as possible, so only one digit should suffice for the suffix.

Upvotes: 2

unniks
unniks

Reputation: 166

Something like this will do. Hope the Customer name is unique.

;WITH cte_emp AS
(
SELECT CustomerName , ROW_NUMBER() over (order by CustomerName) slno
  FROM Customer
  )
UPDATE t SET CustomerName = 'Customer ' + cast(slno as varchar)
  FROM Customer t
 INNER JOIN cte_emp c ON t.CustomerName = c.CustomerName

Upvotes: 1

Related Questions