Update multiple rows with a specific sequence

Question

Post reply

Update multiple rows with a specific sequence

Neel 7777

Right there with Babe

Points: 778
More actions
February 12, 2013 at 4:19 am

#270623

Can anyone help me to build a sql query for updating multiple rows with different id.
Sample Data, ACTCODE datatype nchar(6)
ACTCODE ACTDESC
110001 J's Dream
110003 FPAB
110009 Jharna Dhara
(Where ACTCODE BETWEEN 110001 AND 110009)
will be updated with different ACTCODE in a sequence with incremented by +1
220004 J's Dream
220005 FPAB
220006 Jharna Dhara

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

Might look into the ROW_NUMBER() function.

CREATE TABLE #TmpTbl (ACTCODE NCHAR(6));

INSERT INTO #TmpTbl (ACTCODE) VALUES ('110001'), ('110003'), ('110009');

DECLARE @SEQUENCE NCHAR(6) = 220000;

SELECTACTCODE

, @SEQUENCE + ROW_NUMBER() OVER (ORDER BY ACTCODE)

FROM#TmpTbl;

DROP TABLE #TmpTbl;

_____________________________________________________________________
- Nate

@nate_hughes

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

--this would be your original source table

IF OBJECT_ID('tempdb..#SourceTable') IS NOT NULL

DROP TABLE #SourceTable

CREATE TABLE #SourceTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[Name] NVARCHAR(50) NULL,

PRIMARY KEY (ID))

SET IDENTITY_INSERT #SourceTable ON

INSERT INTO #SourceTable (ID,Name)

SELECT 110001,'J''s Dream'

UNION

SELECT 110003,'FPAB'

UNION

SELECT 110009,'Jharna Dhara'

SET IDENTITY_INSERT #SourceTable OFF

--this returns your original data

SELECT * FROM #SourceTable

If you are converting a large amount of data (say more than 100,000 rows) I'd recommend that the target table be a real table and not a temp table. If you have millions of rows you probably want to break the job into batches.

--create a temp table to hold the data

--and generate the new IDENTITY sequence

IF OBJECT_ID('tempdb..#TargetTable') IS NOT NULL

DROP TABLE #TargetTable

CREATE TABLE #TargetTable (

[ID] INT IDENTITY(22004,1) NOT NULL,

[Name] NVARCHAR(50) NULL,

PRIMARY KEY (ID))

INSERT INTO #TargetTable (Name)

SELECT Name

FROM #SourceTable

--delete the old data (make sure you have a backup)

TRUNCATE TABLE #SourceTable

--now insert the data with the new ID sequence

SET IDENTITY_INSERT #SourceTable ON

INSERT INTO #SourceTable (ID,Name)

SELECT ID,Name

FROM #TargetTable

SET IDENTITY_INSERT #SourceTable OFF

--this is your transformed data

SELECT * FROM #SourceTable

Neel 7777 Right there with Babe Points: 778 More actions · Answer 3

Actually I want to update by set command for multiple rows containing the ACTCODE in a sequence like (001, 002, 003 ETC) with the sample provided earlier.

Sample Data, ACTCODE datatype nchar(6)

ACTCODE ACTDESC

110001 J's Dream

110003 FPAB

110009 Jharna Dhara

(Where ACTCODE BETWEEN 110001 AND 110009)

will be updated with different ACTCODE in a sequence with incremented by +1

220004 J's Dream

220005 FPAB

220006 Jharna Dhara

By set command

RP_DBA SSCertifiable Points: 5116 More actions · Answer 4

Might not be the most efficient way but seems to meet your criteria, no?

CREATE TABLE #TmpTbl (RowId INT IDENTITY(1,1), ACTCODE NCHAR(6));

INSERT INTO #TmpTbl (ACTCODE) VALUES ('110001'), ('110003'), ('110009'), ('120000'), ('120001');

DECLARE @SEQUENCE NCHAR(6) = 220000;

SELECT * FROM #TmpTbl;

UPDATEtmp

SETACTCODE = s.sACTCODE

FROM#TmpTbl tmp

INNER JOIN (

SELECTACTCODE

, sACTCODE = @SEQUENCE + ROW_NUMBER() OVER (ORDER BY ACTCODE)

FROM#TmpTbl

WHEREACTCODE BETWEEN 110001 AND 110009

) s ON tmp.ACTCODE = s.ACTCODE;

SELECT * FROM #TmpTbl;

DROP TABLE #TmpTbl;

_____________________________________________________________________
- Nate

@nate_hughes