SQL Scanning Table to Update Value
I have the following table
Key ID Value
1 1 C
2 1 C
3 1 I
4 2 C
5 2 C
6 2 C
7 1 C
If the value of the value column is I, then I want to update the previous records with the same ID
before I
.
For example ID
1 has the last recod with a value I
, so you want to update the first 2 records of ID
1 to I
. But id 1 with key 7 shouldn't change
I can independently join and update previous records using a Key value less than the current key value, etc. But the table has a large number of records, so it takes a long time to scan through the table for each Id value. Can I use the delay function, but the offset value will be the whole table. So not sure if this is the best solution. Or is there another option besides self-joining and delaying.
source to share
Try this (or an alternative below the results):
SET NOCOUNT ON;
DECLARE @Data TABLE
(
[Key] INT NOT NULL,
[ID] INT NOT NULL,
[Value] VARCHAR(50) NOT NULL
);
INSERT INTO @Data VALUES (1, 1, 'C');
INSERT INTO @Data VALUES (2, 1, 'C');
INSERT INTO @Data VALUES (3, 1, 'I');
INSERT INTO @Data VALUES (4, 2, 'C');
INSERT INTO @Data VALUES (5, 2, 'C');
INSERT INTO @Data VALUES (6, 2, 'C');
INSERT INTO @Data VALUES (7, 1, 'C');
;WITH cte AS
(
SELECT DISTINCT tmp.ID,
MAX(tmp.[Key]) OVER(PARTITION BY tmp.[ID]) AS [MaxKeyOfI]
FROM @Data tmp
WHERE tmp.[Value] = 'I'
)
UPDATE tbl
SET tbl.[Value] = 'I'
--SELECT tbl.*
FROM @Data tbl
INNER JOIN cte
ON cte.[ID] = tbl.[ID]
AND cte.MaxKeyOfI > tbl.[Key]
WHERE tbl.Value <> 'I';
SELECT *
FROM @Data;
Return:
Key ID Value 1 1 I 2 1 I 3 1 I 4 2 C 5 2 C 6 2 C 7 1 C
And if you change Key 3 to C and Key 7 to I, it changes Keys 1 - 3, leaving only keys 4-6.
OR , you can try the following, which finds the values [Key]
and uses them in the UPDATE so that the UPDATE itself is based on the PK field only:
;WITH maxkeys AS
(
SELECT DISTINCT tmp.ID,
MAX(tmp.[Key]) OVER(PARTITION BY tmp.[ID]) AS [MaxKeyOfI]
FROM @Data tmp
WHERE tmp.[Value] = 'I'
), others AS
(
SELECT tmp2.[Key],
tmp2.[ID]
FROM @Data tmp2
INNER JOIN maxkeys
ON maxkeys.[ID] = tmp2.[ID]
AND maxkeys.MaxKeyOfI > tmp2.[Key]
WHERE tmp2.Value <> 'I'
)
UPDATE tbl
SET tbl.[Value] = 'I'
--SELECT tbl.*
FROM @Data tbl
INNER JOIN others tmp
ON tmp.[Key] = tbl.[Key];
source to share
This should work:
WITH CTE AS
(
SELECT *,
MAX([Key]) OVER(PARTITION BY ID) MaxKey
FROM dbo.YourTable
)
UPDATE A
SET Value = 'I'
FROM CTE A
WHERE EXISTS(SELECT 1 FROM CTE
WHERE [Key] = A.MaxKey
AND Value = 'I');
Here is a sqlfiddle with a demo of it.
And the results:
âââââââĶâââââĶââââââââ â Key â ID â Value â â ââââââŽâââââŽââââââââĢ â 1 â 1 â I â â 2 â 1 â I â â 3 â 1 â I â â 4 â 2 â C â â 5 â 2 â C â â 6 â 2 â C â âââââââĐâââââĐââââââââ
source to share
Here's one way. It uses CTEs to find ids that match the condition of the last line being 'I'
. Then it uses this list to update:
with ids as (
select id
from (select ft.*, row_number() over (partition by id order by [key] desc) as seqnum
from followingtable ft
) t
where seqnum = 1 and value = 'I'
)
update followingtable
set value = 'I'
where id in (select id from ids);
EDIT:
You can identify groups of neighboring IDs using a different approach row_numbers()
. Thus, this identifies the groups:
select t.*,
(row_number() over (order by [key]) - row_number() over (partition by id order by [key]) as grp
from followingtable t;
Then you can use this information in the following query:
with ft as (
select t.*,
(row_number() over (order by [key]) - row_number() over (partition by id order by [key])) as grp
from followingtable t;
),
idgrp as (
select id, grp
from (select ft.*, row_number() over (partition by id, grp order by [key] desc) as seqnum
from followingtable ft
) t
where seqnum = 1 and value = 'I'
)
update ft
set value = 'I'
where exists (select 1 from idgrp where idgrp.id = ft.id and idgrp.grp = ft.grp);
source to share
Use Window Function
to find the last value in a group
CREATE TABLE #test1([Key] INT,ID INT,Value VARCHAR(2))
INSERT #test1
VALUES (1,1,'C' ),(2,1,'C' ),(3,1,'I' ),
(4,2,'C' ),(5,2,'C' ),(6,2,'C' )
;WITH cte
AS (SELECT Row_number() OVER(partition BY ID ORDER BY [key] DESC) rn,
*
FROM #test1)
UPDATE A
SET Value = b.value
FROM #test1 A
JOIN cte b
ON a.ID = b.ID
AND b.Value = 'I'
WHERE rn = 1
source to share