March 9, 2011 at 3:56 am
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
March 9, 2011 at 4:18 am
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
March 9, 2011 at 1:42 pm
Hmmm.... I wonder if the batch numbers will always be sequential? If not, the count thing probably won't work.
--Jeff Moden
March 10, 2011 at 1:23 am
Are you sure, Jeff?
If I understand it correctly, the sort order should not matter.
-- Gianluca Sartori
March 10, 2011 at 2:22 am
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.
March 10, 2011 at 2:29 am
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
March 10, 2011 at 4:16 am
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
March 10, 2011 at 4:22 am
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
March 10, 2011 at 9:03 pm
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
March 10, 2011 at 9:16 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply