How to update with new ID

• vijay_uitrgpv

  SSC-Addicted

  Points: 405

  More actions

  April 23, 2018 at 4:48 am

  #344491

  Declare @a table ( id int, id_2 int)
Insert into @a( ID, id_2)
Select 1, 2
union all
Select 2, 3
union All
Select 3, 4
union All
Select 4, 5
union All
Select 10, 11
union All
Select 13, 14

  Select

  * from @a

  

  How can I get the below result set
  

  The business concept is that if we have same data value is id and ID_2 and it will keep search till last highest ID_2 and Update the New_ID with that. Please help me to solve this problem..

• Rick-153145

  SSCrazy

  Points: 2823

  More actions

  April 23, 2018 at 5:31 am

  #1987866

  Could you explain how rows 1-4 are related? They are different ids in your example. Also, explain what you are trying to achieve and post DDL as it will make helping you easier.

• vijay_uitrgpv

  SSC-Addicted

  Points: 405

  More actions

  April 23, 2018 at 6:13 am

  #1987868

  

  There is Zigzag data relationship  and the last Manager ID has to be updated for all record which has this relationship

  

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  April 23, 2018 at 6:14 am

  #1987869

  vijay_uitrgpv - Monday, April 23, 2018 4:48 AM

  Declare @a table ( id int, id_2 int)
  Insert into @a( ID, id_2)
  Select 1, 2
  union all
  Select 2, 3
  union All
  Select 3, 4
  union All
  Select 4, 5
  union All
  Select 10, 11
  union All
  Select 13, 14

  Select

  * from @a

  

  How can I get the below result set
  

  The business concept is that if we have same data value is id and ID_2 and it will keep search till last highest ID_2 and Update the New_ID with that. Please help me to solve this problem..

  I am 200% confident that somebody will give better solution than this ...

  
create table cal_a
( id int, id_2 int);
insert into cal_a( ID, id_2)
Select 1, 2
union all
Select 2, 3
union All
Select 3, 4
union All
Select 4, 5
union All
Select 10, 11
union All
Select 13, 14;
alter table cal_a
add new_id int null;


  I am using 2 update to get the solution:

  
UPDATE cal_a SET new_id =
(Select MAX(a.id_2)from cal_a as A cross apply cal_a as B
where a.id=b.id_2)
where id in (Select b.id from cal_a as A cross apply cal_a as B
where a.id=b.id_2
union
Select a.id from cal_a as A cross apply cal_a as B
where a.id=b.id_2);

  update cal_a SET new_id =id_2
where new_id is null;


  I will also try to find a better solution than this ...

  Saravanan

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 23, 2018 at 6:19 am

  #1987870

  Depending on the logic, there are 2 options. Be sure to understand them as they're very different.
  
Declare @a table ( id int, id_2 int)
Insert into @a( ID, id_2)
Select 1, 2
union all
Select 2, 3
union All
Select 3, 4
union All
Select 4, 5
union All
Select 10, 11
union All
Select 13, 14;

  WITH CTE AS(
  Select *, id - ROW_NUMBER() OVER(ORDER BY id) group_id
  FROM @a
)
SELECT id,
  CTE.id_2,
  MAX(CTE.id_2) OVER(PARTITION BY CTE.group_id)
FROM CTE;

  WITH rCTE AS(
  SELECT id, id_2, id_2 AS New_ID
  FROM @a AS a
  WHERE NOT EXISTS(SELECT * FROM @a AS a2 WHERE a2.id = a.id_2)
  UNION ALL
  SELECT a.id, a.id_2, r.New_ID
  FROM @a AS a
  JOIN rCTE AS r ON a.id_2 = r.id
)
SELECT rCTE.id,
   rCTE.id_2,
   rCTE.New_ID
FROM rCTE
ORDER BY id;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 23, 2018 at 6:21 am

  #1987871

  You should probably scratch what I mentioned and read what I wrote on this article:
  http://www.sqlservercentral.com/articles/set-based+loop/127670/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• vijay_uitrgpv

  SSC-Addicted

  Points: 405

  More actions

  April 23, 2018 at 6:29 am

  #1987874

  Luis Cazares - Monday, April 23, 2018 6:19 AM

  Depending on the logic, there are 2 options. Be sure to understand them as they're very different.
  
  Declare @a table ( id int, id_2 int)
  Insert into @a( ID, id_2)
  Select 1, 2
  union all
  Select 2, 3
  union All
  Select 3, 4
  union All
  Select 4, 5
  union All
  Select 10, 11
  union All
  Select 13, 14;

  WITH CTE AS(
    Select *, id - ROW_NUMBER() OVER(ORDER BY id) group_id
    FROM @a
  )
  SELECT id,
    CTE.id_2,
    MAX(CTE.id_2) OVER(PARTITION BY CTE.group_id)
  FROM CTE;

  WITH rCTE AS(
  SELECT id, id_2, id_2 AS New_ID
  FROM @a AS a
  WHERE NOT EXISTS(SELECT * FROM @a AS a2 WHERE a2.id = a.id_2)
  UNION ALL
  SELECT a.id, a.id_2, r.New_ID
  FROM @a AS a
  JOIN rCTE AS r ON a.id_2 = r.id
)
SELECT rCTE.id,
   rCTE.id_2,
   rCTE.New_ID
FROM rCTE
ORDER BY id;


  Thank you very much this is working as expected

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  April 23, 2018 at 6:34 am

  #1987875

  Luis Cazares - Monday, April 23, 2018 6:19 AM

  Depending on the logic, there are 2 options. Be sure to understand them as they're very different.
  
  Declare @a table ( id int, id_2 int)
  Insert into @a( ID, id_2)
  Select 1, 2
  union all
  Select 2, 3
  union All
  Select 3, 4
  union All
  Select 4, 5
  union All
  Select 10, 11
  union All
  Select 13, 14;

  WITH CTE AS(
    Select *, id - ROW_NUMBER() OVER(ORDER BY id) group_id
    FROM @a
  )
  SELECT id,
    CTE.id_2,
    MAX(CTE.id_2) OVER(PARTITION BY CTE.group_id)
  FROM CTE;

  WITH rCTE AS(
  SELECT id, id_2, id_2 AS New_ID
  FROM @a AS a
  WHERE NOT EXISTS(SELECT * FROM @a AS a2 WHERE a2.id = a.id_2)
  UNION ALL
  SELECT a.id, a.id_2, r.New_ID
  FROM @a AS a
  JOIN rCTE AS r ON a.id_2 = r.id
)
SELECT rCTE.id,
   rCTE.id_2,
   rCTE.New_ID
FROM rCTE
ORDER BY id;


  Awesome...

  Saravanan

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 23, 2018 at 6:35 am

  #1987876

  vijay_uitrgpv - Monday, April 23, 2018 6:29 AM

  Thank you very much this is working as expected

  Have you read the article I mentioned? It offers a great performance improvement.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• vijay_uitrgpv

  SSC-Addicted

  Points: 405

  More actions

  April 23, 2018 at 6:40 am

  #1987880

  I have set this in my bookmark to read it later.. Thanks for sharing this.