Update Duplicate Records dynamically

Question

Update Duplicate Records dynamically

Honeeysha

Old Hand

Points: 304
More actions
November 15, 2017 at 7:36 am

#402672

Can you anyone please tell the SQL query on how to update the duplicate records dynamically.

PRIMARY_KEY VERSION ISCURRENT BOOKING_NUM
1 1 0 ABCD
2 2 0 ABCD
3 3 1 ABCD
4 4 1 ABCD

I want to keep the latest record ISCURRENT = 1 and rest should all should be ISCURRENT = 0. Below is the table exactly what I want, please

PRIMARY_KEY VERSION ISCURRENT BOOKING_NUM
1 1 0 ABCD
2 2 0 ABCD
3 3 0 ABCD
4 4 1 ABCD

Thanks,
Samuel

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

This is a a bit of guess work, as your table as a PRIMARY_KEY and Version column with the same values. As we only have 1 example, guessing that the partition should on BOOKING_NUM. Perhaps:

USE Sandbox;
GO
CREATE TABLE YourTable
  (PRIMARY_KEY int,
   [VERSION] int,
   ISCURRENT bit,
   BOOKING_NUM char(4)); --Num implies Number, so why does this contain alphas?
INSERT INTO YourTable
VALUES
  (1,1,0,'ABCD'),
   (2,2,0,'ABCD'),
   (3,3,1,'ABCD'),
   (4,4,1,'ABCD');
GO
SELECT *
FROM YourTable;
WITH CTE AS (
  SELECT *,
     ROW_NUMBER() OVER (PARTITION BY BOOKING_NUM ORDER BY VERSION DESC) AS RN
   FROM YourTable)
UPDATE CTE
   SET ISCURRENT = CASE RN WHEN 1 THEN 1 ELSE 0 END;
SELECT *
FROM YourTable;
GO
DROP TABLE YourTable;
GO

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Honeeysha Old Hand Points: 304 More actions · Answer 2

Hi Thom,

Thank you so much the query worked like a charm 🙂
I have another issue can you help me with the query, please?

TRANS_PKEY	VERSION	ISCURRENT	BOOKING_NUM
7883191	1	0	ABCD
7883192	2	0	ABCD
7883193	2	0	ABCD
7883194	3	0	ABCD
7883195	4	0	ABCD
7883196	4	1	ABCD

I want to update the above table Version dynamically as per the below please:

TRANS_PKEY	VERSION	ISCURRENT	BOOKING_NUM
7883191	1	0	ABCD
7883192	2	0	ABCD
7883193	3	0	ABCD
7883194	4	0	ABCD
7883195	5	0	ABCD
7883196	6	1	ABCD

Many Thanks,
Raj

Thom A SSC Guru Points: 99130 More actions · Answer 3

The logic is the same as above, but instead set your value to that of your RN. This suggests you don't quite understand what the solution I supplied does; do you?

Have a go yourself and if you get stuck post back with what you tried.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Honeeysha Old Hand Points: 304 More actions · Answer 4

Hi Thom,

Below is the query I've tried and it working.

GO
SELECT *FROM YourTable;
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY BOOKING_NUM ORDER BY TRANS_PKEY ASC) AS RN FROM YourTable)
UPDATE CTE SET VERSION = RN;
SELECT *FROM YourTable;
GO

PRIMARY_KEY	VERSION	ISCURRENT	BOOKING_NUM
1	1	0	ABCD
2	2	0	ABCD
3	3	1	ABCD
4	4	1	ABCD