Code: Select all
dbadmin=> SELECT * FROM x ORDER BY 1;
c1 | c2
----+---------
1 | Jim
2 | Peng
3 | Tom
4 | Mark
5 | Patrick
(5 rows)
To accomplish this we can use the analytic function LEAD to grab the next value of C2 for every row and use it in an UPDATE statement:
Code: Select all
dbadmin=> UPDATE x
dbadmin-> SET c2 = (SELECT lead_c2
dbadmin(> FROM (SELECT c1, LEAD (c2, 1) OVER (ORDER BY c1) lead_c2
dbadmin(> FROM x) foo
dbadmin(> WHERE foo.c1 = x.c1);
OUTPUT
--------
5
(1 row)
dbadmin=> SELECT * FROM x ORDER BY 1;
c1 | c2
----+---------
1 | Peng
2 | Tom
3 | Mark
4 | Patrick
5 |
(5 rows)
We need to slightly modify our UPDATE statement to include another analytic function called FIRST_VALUE. We’ll use that value when the LEAD function returns a NULL.
First, I restored the original data in table X:
Code: Select all
dbadmin=> SELECT * FROM x ORDER BY 1;
c1 | c2
----+---------
1 | Jim
2 | Peng
3 | Tom
4 | Mark
5 | Patrick
(5 rows)
Code: Select all
dbadmin=> UPDATE x
dbadmin-> SET c2 = (SELECT new_c2
dbadmin(> FROM (SELECT c1,
dbadmin(> NVL(LEAD (c2, 1) OVER (ORDER BY c1),
dbadmin(> FIRST_VALUE(c2) OVER (ORDER BY c1)) new_c2
dbadmin(> FROM x) foo
dbadmin(> WHERE foo.c1 = x.c1);
OUTPUT
--------
5
(1 row)
dbadmin=> SELECT * FROM x ORDER BY c1;
c1 | c2
----+---------
1 | Peng
2 | Tom
3 | Mark
4 | Patrick
5 | Jim
(5 rows)
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
Have fun!