SHARE
TWEET

Untitled

a guest Jun 15th, 2019 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. | Id  |  lIST_1      |
  2.    ----------------------
  3.    | 1   |  NULL        |
  4.    | 2   |  JASON       |
  5.    | 3   |  NULL        |    
  6.    | 4   |  BANDORAN    |
  7.    | 5   |  NULL        |
  8.    | 6   |  NULL        |
  9.    | 7   |  SMITH       |
  10.    | 8   |  NULL        |
  11.      
  12. | Id  |  lIST_1      
  13.    -----------------------
  14.    | 1   |  JASON        |
  15.    | 2   |  BANDORAN     |
  16.    | 3   |  SMITH        |  
  17.    | 4   |  NULL         |
  18.    | 5   |  NULL         |
  19.    | 6   |  NULL         |
  20.    | 7   |  NULL         |
  21.    | 8   |  NULL         |
  22.      
  23. select row_number() over (order by (select null)) as id, t.list_1
  24. from t
  25. order by (case when list_1 is not null then 1 else 2 end)
  26.      
  27. with toupdate as (
  28.       select row_number() over (order by (case when list_id is not null then 1 else 2 end), id
  29.                                ) as new_id,
  30.              t.*
  31.       from t
  32.      )
  33. update toupdate
  34.     set id = new_id
  35.     where id <> new_id;  -- no need to update if the value remains the same
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top