Update table

Question

Post reply

Update table

sandy-833685

SSC Eights!

Points: 919
More actions
March 9, 2011 at 3:56 am

#389011

Hi,
I have a table called batch
which is having data for example
empcodebatch
2491
2492
2503
2514
2523
2524
2531
2546
2547
2551
2552
now where ever i find the repeat of empcode i want to update any one empcode with empcode+batch for example empcode 249 is having two different batch 1 & 2.So i need to update 2491 against 1 bacth or 2492 against 2 batch in empcode column instead of 249 and similary for 252, it will be 2523 or 2524 .Thx

Viewing 10 posts - 1 through 9 (of 9 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This should do the trick for you:

-- DECLARE TEST TABLE

DECLARE @batch TABLE (

empcode varchar(50),

batch int

)

-- POPULATE WITH SAMPLE DATA

INSERT INTO @batch

SELECT 249,1

UNION ALL SELECT 249,2

UNION ALL SELECT 250,3

UNION ALL SELECT 251,4

UNION ALL SELECT 252,3

UNION ALL SELECT 252,4

UNION ALL SELECT 253,1

UNION ALL SELECT 254,6

UNION ALL SELECT 254,7

UNION ALL SELECT 255,1

UNION ALL SELECT 255,2

-- BEFORE

SELECT *

FROM @batch

-- UPDATE

;WITH RepeatingBatches AS (

SELECT *, CNT = COUNT(*) OVER (PARTITION BY empcode)

FROM @batch

)

UPDATE RepeatingBatches

SET empcode = empcode + CAST(batch AS varchar(10))

WHERE CNT > 1

-- AFTER

SELECT *

FROM @batch

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

Hmmm.... I wonder if the batch numbers will always be sequential? If not, the count thing probably won't work.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Are you sure, Jeff?

If I understand it correctly, the sort order should not matter.

-- Gianluca Sartori

sandy-833685 SSC Eights! Points: 919 More actions · Answer 4

Hi Gianluca,

Thx but the union will work if i am having only limited records but what if i am having 1 lakh records or more than that. i mean to say how do i write select statement with union than.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

I'm not sure I understand your question.

Everything before the keyword "WITH" is not part of the solution, is just a setup of sample data to show how the example works. You won't need running it.

-- Gianluca Sartori

sandy-833685 SSC Eights! Points: 919 More actions · Answer 6

Hi Gianluca,

It is updating all the records which are repeadted i wont need all the records to be updated. i need any one record to updated from the repeated one's.

the output of your result this.

Empcode Batch

24911

24922

2503

2514

25233

25244

2531

25466

25477

25511

25522

but wherein it should be like this

2491

24922

2503

2514

2523

25244

2531

2546

25477

2551

25522

my intention is to make it unique. for your information there is one more column called id which is working as rowid. i.e. id,code,batch

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

OK then, this should do.

-- DECLARE TEST TABLE

DECLARE @batch TABLE (

id int identity(1,1),

empcode varchar(50),

batch int

)

-- POPULATE WITH SAMPLE DATA

INSERT INTO @batch (empcode, batch)

SELECT 249, 1

UNION ALL SELECT 249, 2

UNION ALL SELECT 250, 3

UNION ALL SELECT 251, 4

UNION ALL SELECT 252, 3

UNION ALL SELECT 252, 4

UNION ALL SELECT 253, 1

UNION ALL SELECT 254, 6

UNION ALL SELECT 254, 7

UNION ALL SELECT 255, 1

UNION ALL SELECT 255, 2

-- BEFORE

SELECT *

FROM @batch

-- UPDATE

;WITH RepeatingBatches AS (

SELECT *,

CNT = COUNT(*) OVER (PARTITION BY empcode),

RN = ROW_NUMBER() OVER (PARTITION BY empcode ORDER BY empcode, id)

FROM @batch

)

UPDATE RepeatingBatches

SET empcode = empcode + CAST(batch AS varchar(10))

WHERE CNT > 1 AND RN > 1

-- AFTER

SELECT *

FROM @batch

I would still wait for Jeff's reply, I might have overlooked something.

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 8

Gianluca Sartori (3/10/2011)
Are you sure, Jeff?
If I understand it correctly, the sort order should not matter.

No. My bad. I didn't read the requirements correctly. My apologies. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 9

Gianluca Sartori (3/10/2011)
I would still wait for Jeff's reply, I might have overlooked something.

You pretty much sussed it, Gianluca. At this point, all I can do is to recommend simplification because you don't need the CNT... if the rownumber isn't over 1, it won't be updated which implicitly does the logic equivalent of a count.

WITH

cteEnumerateOccurances AS

(

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY empcode ORDER BY empcode, batch),

empcode,

batch

FROM @batch

)

UPDATE tgt

SET empcode = empcode + CAST(batch AS varchar(10))

FROM cteEnumerateOccurances tgt

WHERE RowNum > 1

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)